That's your problem. If you want to do it with code, you need to look at
the precedents that are changed, not the resulting values.

Wouldn't it be easier just to add some conditional formats on A1:C100 to
address the colorindex?



"Antney" wrote:

I'm also using a vlookup in that cell.

"Antney" wrote:

I tried using this code, in my Excel spreadsheet, for my worksheet but it is
not working. Could it be that the cells I'm trying to color are merged?

Any help would be appreciated.

Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.Interior.ColorIndex = 5
Case "Cat"
Target.Interior.ColorIndex = 10
Case "Other"
Target.Interior.ColorIndex = 6
Case "Rabbit"
Target.Interior.ColorIndex = 46
Case "Goat"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub