I have the following code which changes the color of the cell based on its
value.
Now I need to color not only that particular cell, but also 3 cells above
it. How can I put it in a loop.

This is my code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:AQ120")) Is Nothing Then
Select Case Target

Case Is = "A"
icolor = 38
Case Is = "B"
icolor = 38
Case Is = "C"
icolor = 35
Case Is = "D"
icolor = 36
Case Is = "E"
icolor = 39
Case Is = "F"
icolor = 35
Case Is = "G"
icolor = 37
Case Is = "H"
icolor = 34
Case Is = "I"
icolor = 40
Case Is = "J"
icolor = 40
Case Is = "K"
icolor = 34
Case Is = "L"
icolor = 34
Case Is = "M"
icolor = 34
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

If the three cells above Z100 are Z99, Z98, and Z97 then

Instead of:
Target.Interior.ColorIndex = icolor
use something like
Dim r as Range
Set r=Range(Target, Target.Offset(-3,0))
r.Interior.ColorIndex = icolor
--
Gary's Student


"Rajula" wrote:

I have the following code which changes the color of the cell based on its
value.
Now I need to color not only that particular cell, but also 3 cells above
it. How can I put it in a loop.

This is my code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:AQ120")) Is Nothing Then
Select Case Target

Case Is = "A"
icolor = 38
Case Is = "B"
icolor = 38
Case Is = "C"
icolor = 35
Case Is = "D"
icolor = 36
Case Is = "E"
icolor = 39
Case Is = "F"
icolor = 35
Case Is = "G"
icolor = 37
Case Is = "H"
icolor = 34
Case Is = "I"
icolor = 40
Case Is = "J"
icolor = 40
Case Is = "K"
icolor = 34
Case Is = "L"
icolor = 34
Case Is = "M"
icolor = 34
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub