Hi Dave...

Thanks !!!! :)

I will give this a try and see how it goes !

Take care and KIP

IJ :)


"Dave Peterson" wrote:

"D4:D21" "D27:D87" is not a single string, so the assignment will fail right
there.

And sometimes working with lots of addresses can cause problems. I'd do
something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim WS_RANGEALL As Range

Const WS_RANGE1 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"

Const WS_RANGE2 As String = _
"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"

Const WS_RANGE3 As String = _
"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"

Const WS_RANGE4 As String = _
"P834:P851,P857:P874,P880:P897,P903:P920,P926:P943 ,V4:V21," & _
"V27:V87,V93:V153,V164:V224,U274:U344,U340:U400,U4 06:U466," & _
"U472:U532,U538:U598,V627:V644,U673:U690,U696:U713 ,U719:U736"

Const WS_RANGE5 As String = _
"V765:V872,V788:V805,V811:V828,V834:V851,V857:V874 ,V880:V897," & _
"V903:V920,V926:V943"

Set WS_RANGEALL = Union(Me.Range(WS_RANGE1), _
Me.Range(WS_RANGE2), Me.Range(WS_RANGE3), _
Me.Range(WS_RANGE4), Me.Range(WS_RANGE5))

If Target.Cells.Count 1 Then Exit Sub

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, WS_RANGEALL) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If .Value 0 Then
.Value = .Value * -1
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


IJ wrote:

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 :)



--

Dave Peterson