Setting cell background color based on value

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

Guest

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.
 
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.
 
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
 
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
 
Back
Top