No colour in cells when blank in list
 

I have the following code already, so when the cell is 0 or 100, it turns
red, 30 turns blue, etc.

However, if I have a blank cell it shows red and I don't want it to have any
colour at all, just the default 'no fill'.

Can anybody help. Many thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Set Target = Intersect(Target, Range("E:E"))
If Not Target Is Nothing Then
For Each rArea In Target
For Each rCell In rArea
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(23, 178, 233)
Case 60
nColor = RGB(245, 200, 11)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = RGB(255, 255, 255)
End Select
If Not nColor = -1 Then _
rCell.Offset(0, -4).Interior.Color = nColor
Next rCell
Next rArea
End If
End Sub

No colour in cells when blank in list
 

Add this...

Case ""

in front of all the other Case statements and set the nColor variable to
what you want in there.

Rick


"harwookf" wrote in message
...
I have the following code already, so when the cell is 0 or 100, it turns
red, 30 turns blue, etc.

However, if I have a blank cell it shows red and I don't want it to have
any
colour at all, just the default 'no fill'.

Can anybody help. Many thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Set Target = Intersect(Target, Range("E:E"))
If Not Target Is Nothing Then
For Each rArea In Target
For Each rCell In rArea
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(23, 178, 233)
Case 60
nColor = RGB(245, 200, 11)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = RGB(255, 255, 255)
End Select
If Not nColor = -1 Then _
rCell.Offset(0, -4).Interior.Color = nColor
Next rCell
Next rArea
End If
End Sub