Condintional Format Entire Row based on Value in Cell

W

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
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top