Simple Conditional Format with more than 3 options

[Lionel B. Dyck]

Is there a simple way to extend conditional formatting to cover more than 3
conditions?

thanks

 

[Dave Peterson]

Upgrade to xl2007.

Or use an event macro that replaces the conditional formatting???

 

[Gord Dibben]

If CF'ing numerics, John MCGrimpsey shows how to get up to 6 different font
colors.

http://www.mcgimpsey.com/excel/conditional6.html

For anything else you are forced to use event code similar to this.

Option Compare Text
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("D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

One more thing I just thought about.

You can try CFPlus add-in from Bob Phillips' site.

Allows up to 30 conditions.

http://www.xldynamic.com/source/xld.CFPlus.Download.html


Gord

 

[Lionel B. Dyck]

If CF'ing numerics, John MCGrimpsey shows how to get up to 6 different font
colors.

http://www.mcgimpsey.com/excel/conditional6.html

For anything else you are forced to use event code similar to this.

Option Compare Text
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("D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

Thank you - something new to learn and try. Very much appreciated.