Increasing Conditional Formatting

[Phil Hageman]

What would be the approach to programatically increase
conditional formatting on a worksheet - beyond the limit
of three in Excel - that operates the same way?

 

[Bob Phillips]

Phil,

Use worksheet event code. You could trap the worksheet change event for the
colouring to be set when the value changes, or the calculate event for any
sheet change.

Example of the former

Private Sub Worksheet_Change(By Target As Range)
Application.Enable = False
On Error GoTo ws_exit:
If (Not Intersect(Target, Range("colours")) Is Nothing) Then
Select Case Target.Value
Case "a": Target.Interior.ColorIndex = 3
Case "b": Target.Interior.ColorIndex = 4

Case "c": Target.Interior.ColorIndex = 34

Case "d": Target.Interior.ColorIndex = 35
etc.
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub