Setting cell background color based on value
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
On Wed, 25 Feb 2004 09:16:13 -0800, "Erik"
wrote:
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.
|