Private sub worksheet_change(bval target as range) issue
 

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

Private sub worksheet_change(bval target as range) issue
 

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)

"Darren" wrote in message
...
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

Private sub worksheet_change(bval target as range) issue
 

Thanks much, that works great.

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

Thanks again.

"Bob Phillips" wrote:

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)

"Darren" wrote in message
...
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