View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
BeSmart BeSmart is offline
external usenet poster
 
Posts: 102
Default Need help to changes a conditional formatting code....


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?
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