Conditional Formatting limited to 3 conditions

  • Thread starter Thread starter Scott Fagan
  • Start date Start date
S

Scott Fagan

Has anyone found a way around the limit of three
conditions when using conditional formatting? I could
really use 6 conditions.

Does anyone know of a third party add-in or patch to
increase the limit beyond 3 conditions.

Thanks,

Scott
 
Scott

Don't know of a third-party add-in, but Worksheet Event code in the Worksheet
module could suffice.

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 <= 0: Num = 10 'green
Case 1 To 5: Num = 1 'black
Case 6 To 10: Num = 5 'blue
Case 11 To 15: Num = 7 'magenta
Case 16 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select
'Apply the color
Rng.Interior.ColorIndex = Num
''Rng.Font.ColorIndex = Num (alternatively color Font)
Next Rng
End Sub

Right-click on sheet tab and paste code into module. Operates on column A
only. Adjust this and number ranges to suit.

Gord Dibben Excel MVP XL2002
 
Back
Top