Coloring cells

[Patrick Simonds]

This is the code I use to color a cell based on text in the cell:

If Not Intersect(Target, Range("A5:G56")) Is Nothing Then

Select Case Left(Target.Text, 4)
Case Is = "Sick"
icolor = 38

End Select

How can I also get it to color the 2 cells immediately above it?

 

[Tom Ogilvy]

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

 

[JE McGimpsey]

Your code doesn't color anything...


However, you can try:

Dim iColor As Long
If Not Intersect(Target, Range("A5:G56")) Is Nothing Then
Select Case Left(Target.Text, 4)
Case Is = "Sick"
iColor = 38
Case Else
iColor = xlColorIndexNone
End Select
Target.Offset(-2, 0).Resize(3, 1).Interior.ColorIndex = iColor
End If