The code will work if you paste a single cell in that watchrange (A1:C100)?

If you're pasting multiple cells, then the code exits right away:
If Target.Cells.Count 1 Then Exit Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToInspect As Range
Dim myIntersect As Range
Dim myCell As Range
Dim myColor As Long

Set RngToInspect = Me.Range("A1:c100") 'change to suit

Set myIntersect = Intersect(RngToInspect, Target)

If myIntersect Is Nothing Then
'not in the range
Exit Sub
End If

For Each myCell In myIntersect.Cells
myColor = -9999
Select Case LCase(myCell.Value)
Case Is = LCase("dog"): myColor = 5
Case Is = LCase("cat"): myColor = 10
Case Is = LCase("Other"): myColor = 6
Case Is = LCase("Rabbit"): myColor = 46
Case Is = LCase("Goat"): myColor = 45
End Select

If myColor < 0 Then
'do nothing
Else
myCell.Interior.ColorIndex = myColor
End If
Next myCell
End Sub


stevedemo77 wrote:

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?

--

Dave Peterson