Excel's Three-Criteria Limit for Conditional Formatting
You can use VBA to tutorial conditional formatting to use more than three criteria on your data. In fact, you can use the code to apply virtually an unlimited number of criteria.
Excel has a useful feature named conditional formatting. You can find it by selecting Format → Conditional Formatting... on the worksheet menu bar. Conditional formatting enables you to format a cell based on its content. For example, you can change to a red background all cells whose value is greater than 5 but less than 10. Although this is handy, Excel supports only up to three conditions, which sometimes is not enough.
If you want to set more than three conditions, you can use Excel VBA code that is fired automatically whenever a user changes a specified range. To see how this works, say you want to have six separate conditions in the range A1:A10 on a particular worksheet.
Save your workbook, then activate the worksheet, right-click its Sheet Name tab, select View Code, and enter the following code:
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
Close the window to get back to your worksheet, then save your workbook.
The background color of each cell should have changed based on the number passed to the variable icolor, which in turn passes this number to Target.Interior.ColorIndex. The number that is passed is determined by the line Case x To x. For example, if you enter the number 22 in any cell within the range A1:A10, the number 15 is passed to icolor, and then icolor (nowhaving a value of 15) is passed to Target.Interior.ColorIndex, making the cell gray. Target is always the cell that changed and, thus, fired the code.
In this tutorial:
- Retrieve a Workbook's Name and Path
- Excel's Three-Criteria Limit for Conditional Formatting
- Run Procedures on Protected Worksheets
- Distribute Macros
- Automatically Add Date/Time to a Cell upon Entry
- Create a List of Workbook Hyperlinks
- Find a Number Between Two Numbers
- Name a Workbook with the Text in a Cell
- Sort Worksheets
- Password-Protect a Worksheet from Viewing