Condintional Format Entire Row based on Value in Cell

[WOLF87654]

I have this VBA code (see below) which changes the background color of a
cell based on the value in the cell. What I really want to do is apply
this to the entire row so that say “S5” gives the result “DOG” then the
entire row changes to red. Thanks in advance for any suggestions.


Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPage = Range("A5:S200")
For Each Cell In MyPage

If Cell.Value = "CAT" Then
Cell.Interior.ColorIndex = 4
End If

If Cell.Value = "BIRD" Then
Cell.Interior.ColorIndex = 40
End If

If Cell.Value = "DOG" Then
Cell.Interior.ColorIndex = 3
End If

If Cell.Value = "SNAKE" Then
Cell.Interior.ColorIndex = 10
End If

If Cell.Value <> "CAT" And Cell.Value <> "BIRD" And Cell.Value
<> "DOG" And Cell.Value <> "SNAKE" Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub

 

[Per Jessen]

What about:

Cell.EntireRow.Interior.ColorIndex=....

Regards,
Per

 

[Rick Rothstein]

Just add EntireRow to the Cell reference. For example, where you have
this...

Cell.Interior.ColorIndex = 3

change it to this...

Cell.EntireRow.Interior.ColorIndex = 3

and then do the same modification to the rest of your color assignments.