On Sun, 12 Sep 2004 22:09:02 -0700, "BeSmart"
wrote:


Thanks Ron - that worked really well - but you're right, the user will
delete numbers from cells at which time I need the formatting to revert to
none.

I tried inserting the "set colorindex=xln=None" at the beginning as you said
but I got error messages. Obviously I put it in the wrong spot - please show
me where?

==============================================
Private Sub Worksheet_Change(ByVal Target As Range)

For Each c In Target
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case Cells(Target.Row, 5).Text
Case Is = "Cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "Dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "Horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "Camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "Pig"
Target.Cells.Interior.ColorIndex = 7
Case Else
Target.Cells.Interior.ColorIndex = xlNone
End Select
End If
End If
Next c
End Sub
===============================================

Also, I might be wrong here but if users won't need to enter numbers
anywhere except where the conditional formatting is required, restricting a
range shouldn't be necessary right?

So long as you can be certain that they will not enter a number elsewhere
(perhaps by accident). You could do that with protection.

But if you can explain how I write that into the code it might be very
userful in the future. Note, There are two ranges on my spreadsheet:
E14:E605 and M14:GR605.

Thank you SOOOO much for your help!!! It's been extremely helpful.
BeSmart

I'm guessing the user entry area is M14:GR605?

So you could have something like this:

=======================
For Each c In Target
If Not Intersect(c, [M1:GR605]) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "pig"
Target.Cells.Interior.ColorIndex = 7

End Select
End If
End If
End If
Next c
================================

Note that I also made the animal name case independent. If this is not
appropriate, you should remove the LCase function and capitalize the animal
names.


--ron