Private sub worksheet_change(bval target as range) issue

[Guest]

Hi There,

I have a macro that changes the color of the cell "g4" as the cell changes
numerical values in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g4"), Target) Is Nothing Then
If Range("g4").Value > 0 Then
Range("g4").Cells.Interior.ColorIndex = 35
ElseIf Range("g4").Value < 0 Then
Range("g4").Cells.Interior.ColorIndex = 38
Else
Range("g4").Cells.Interior.ColorIndex = xlNone
End If
End If
End Sub

I would like to make it so that the color changes as cell values change for
each cell from "g4" to "g16"

I figure I can copy and paste this code in the private sub and then adjust
the cell reference, but I imagine there is a much more simple and efficient
method that I can't think of.

Thanks for your help

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("G4:G16"), Target) Is Nothing Then
If Target.Value > 0 Then
Target.Cells.Interior.ColorIndex = 35
ElseIf Target.Value < 0 Then
Target.Cells.Interior.ColorIndex = 38
Else
Target.Cells.Interior.ColorIndex = xlNone
End If
End If
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks much, that works great.

I knew it should be easy, it's just I am not that skilled yet.

Thanks again.