Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter a list of numbers and blank cells | Excel Discussion (Misc queries) | |||
Eliminating blank cells in a list on a ROW | Excel Worksheet Functions | |||
List not omitting blank cells....?? | Excel Worksheet Functions | |||
Function to list values of last 3 non-blank cells in a vertical bl | Excel Worksheet Functions | |||
Eliminate creating list that returns blank cells | Excel Worksheet Functions |