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


--
WOLF87654
------------------------------------------------------------------------
WOLF87654's Profile: http://www.thecodecage.com/forumz/member.php?userid=223
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=83449

What about:

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

Regards,
Per

On 7 Apr., 03:43, WOLF87654 wrote:
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

--
WOLF87654
------------------------------------------------------------------------
WOLF87654's Profile:http://www.thecodecage.com/forumz/member.php?userid=223
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=83449

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.

--
Rick (MVP - Excel)


"WOLF87654" wrote in message
...

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


--
WOLF87654
------------------------------------------------------------------------
WOLF87654's Profile:
http://www.thecodecage.com/forumz/member.php?userid=223
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=83449