No colour in cells when blank in list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Add this...

Case ""

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

Rick
 
Back
Top