Mike,
I just found this post, and it works great! It works if you type the value
into the cell, but I need it to execute if data is pasted into the worksheet
and it meets the criteria for each case. Is there something in the code that
can be modified to make that happen rather than having to actually type the
word in each cell?

Thanks,
Steve

"Mike H" wrote:

You can have as many as you want with this. It;s worksheet code so
right-click the tab, view code and paste in.

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

Mike

"Art Caragh" wrote:

In a particular column in my spreadsheet I have set up conditional formats to
change the background colour and font colour if certain words are inserted.
The conditional Formatting option on the tool bar gives me a maximum range
of three.

how can i extend this using VBA?