for 3 rows above

Target.offset(-3,0).Resize(4,1).Interior.ColorIndex = icolor

--
Regards,
Tom Ogilvy





"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