Setting cell background color based on value

E

Erik

Hi

I am trying to set an array of cells' background colors based on their individual text values. Conditional formatting would normally work, but I have 10 different colors to assign and the built in conditional formatting only allows three conditions. Hoping someone could help me with this little project. Thanks.
 
R

Ron de Bruin

You can use the change event with Select Case

Here is a example that use the Change event of the worksheet
Right click on a sheet tab and choose view code.
Paste the event in there and press Alt-Q to go back to Excel.

this will only work in a1:a20


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("a1:a20"), Target) Is Nothing Then
Select Case Target.Value
Case "a"
Target.Interior.ColorIndex = 3
Case "b"
Target.Interior.ColorIndex = 5
Case "c"
Target.Interior.ColorIndex = 8
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End If
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Erik said:
Hi,

I am trying to set an array of cells' background colors based on their individual text values. Conditional formatting would
Click to expand...
normally work, but I have 10 different colors to assign and the built in conditional formatting only allows three conditions.
Hoping someone could help me with this little project. Thanks.
 
G

Gord Dibben

Erik

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub

Change the Case Is = 1 etc to Case Is = "yourtext" etc.

Gord Dibben Excel MVP
 
E

Erik

Ron and Gord

Thank you. Both sets of code work great. One more question. How can I apply the same background color to an adjacent cell regardless of that cell's value

Erik
 
Top