Brain cramp!!!!!---The cells were negative! How would I modify Case for
"CAT" and negative.
Thanks!

"Bob Phillips" wrote:

Bill,

Doesn't stop for me. I had yellow cells in V5, W5, and X5 and Z5 was empty,
and it cleared them nicely.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bill" wrote in message
...
Hi
Im new to VB and I am trying to modify some one else's code.
The code colors the cells according to the cell contents in ColZ. However
if Z is blank the code stops. If Z is blank I would like the cell to be
clear. How do I add the case for blank?

Private Sub Commandbutton1_Click()
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Me.Range("z5:z36")
Select Case LCase(cell.Value)
Case "cat": ColorCell cell, 39
Case "dog": ColorCell cell, 35
Case "fish": ColorCell cell, 34
Case "horse": ColorCell cell, 36
Case Else: ColorCell cell, -4142

End Select
Next
ws_exit:
Application.EnableEvents = True
End Sub

Function ColorCell(rng As Range, idex As Long)
Dim c As Range
With rng
For Each c In .Offset(0, -24).Resize(1, 24)

If c < "" Then
If IsNumeric(c) Then
If c 0 Then c.Interior.ColorIndex = idex
End If
End If
Next
End With
End Function


Thanks
Bill