Oh dear.

You are trying to build one character string.
Because it is so long you will have to do it in bits and join them
together., which you do like this (as has been posted)

string1 = string2 & string3

You can use considerably bigger bits.
A range can be built from different bits separated by a comma
If you have to, or prefer to, use more than one line to build your string
you have to put a space followed by an underline at the end of the first
line.

This example is tested and works.

Const WS_RANGE As String= "D4:D21,D21:D14" _
& ",C38:C59,D6:D8,C17:C21"

Happy glueing.



"IJ" wrote in message
...
Hello,

I am trying to create a worksheet with specific columns that have a
negative
value.

I entered the following into the Visual Basic Code page:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String= "D4:D21" "D27:D87"
"D93:D153" "D164:D224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627:D644" "C673:C690" "C696:C713" "C719:C736" "D765:D782"
"D788:D805" "D811:D828" "D834:D851" "D857:D874" "D880:D897" "D903:D920"
"D926:D943" "J4:J21" "J27:J87" "J93:J153" "J164:J224" "I274:I334"
"I340"I400"
"I406:I466" "I472:I532" "I538":I598" "J627:J644" "I673:I690" "I696:I713"
"I719:I736" "J765:J782" "J788:J805" "J811:J828" "J834:J851" "J857:J874"
"J880:J897" "J903:J920" "J926:J943" "P4:P21" "P27:P87" "P93:P153"
"P164:P224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627:P644"
"O673:O690" "O696:O713" "O719:O736" "P765:P872" "P788:P805" "P811:P828"
"P834:P851" "P857:P874" "P880:P897" "P903:P920" "P926:P943" "V4:V21"
"V27:V87" "V93:V153" "V164:V224" "U274:U344" "U340:U400" "U406:U466"
"U472:U532" "U538:U598" "V627:V644" "U673:U690" "U696:U713" "U719:U736"
"V765:V872" "V788:V805" "V811:V828" "V834:V851" "V857:V874" "V880:V897"
"V903:V920" "V926:V943"


Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value * -1
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub



And the line "Const WS_RANGE As String= "D4:D21" "D27:D87", is all in red
and after this entry in the above string, "D27:D87", I received the
following error code:

"Micrsoft Visual Basic Compile error: Expected: end of statement"

Would anyone know why? And if so, is there a way to correct this error and
if so, how?

Any help or information anyone may be able to provide will be greatly
appreciate...Thanks !!!!

Take care and KIP

IJ :)