• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

7. Macro Hacks > 92. Get Around Excel’s Three-Criteria Limit for Conditional Fo...

Get Around Excel’s Three-Criteria Limit for Conditional Formatting

You can use VBA to hack 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 very useful feature named conditional formatting (described in Chapter 2). 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. Set up some data such as that shown in


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint