Conditional Formatting with Multiple Criteria in Excel

 Subscribe!

Microsoft Excel has a limit of 3 criteria in Conditional Formatting. We can get around this with a bit of VBA code…

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intclr As Integer
If Not Intersect(Target, Range(”A1:z100″)) Is Nothing Then
Select Case Target
Case “ted”
intclr = 6
Case 6
intclr = 12
Case 11 To 15
intclr = 7
Case 16 To 20
intclr = 53
Case 21 To 25
intclr = 15
Case 26 To 30
intclr = 42
Case Else
End Select
Target.Interior.ColorIndex = intclr
End If
End Sub

Enter the range you want to include in the conditional formatting in the line “If Not Intersect(Target, Range(”A1:z100″)) Is Nothing Then”. Select the conditions (some example conditions are in the sample code following “Case”.

The colors that correspond to the colorindex integer can be found at
ColorIndex

Joseph Harris
Excel Help

Related posts:

  1. Conditional Formatting Q: Dear ExcelYogi, Thank you for all your wonderful advise...
  2. ColorIndex Reference - Excel VBA A chart that can be used to look up the...
  3. Playing with VBA in Excel, Without Knowing VBA! Introduction to user defined functions and VBA in Excel....
  4. Sum by the Color of a Cell - Excel Surfing the web today, I came across a great user...
  5. User Passwords in Excel - Multiple Passwords Simple multiple user password protection in Excel....

This entry was posted on Tuesday, February 3rd, 2009 at 3:45 pm and is filed under Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Conditional Formatting with Multiple Criteria in Excel”

  1. randy on March 4th, 2009 at 12:02 pm

    i am using your conditonal formating script in vba for excel but i have one issue i hope you can help, it works fine when you change one cell at a time, but for instance when i highlight a row and hit the del key i get an error, if i hit ignore it does what i want but is there a way to get around that

    thanks

Leave a Reply