G
Guest
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
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