Conditional Formatting (Over built in 3 limit)

This code must be placed in the Private Module of the Worksheet.

This code gets around the limit of 3 conditional formatting options. It is set to work on A1:A10 but this can be changed.

Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub

Code Snippits