Conditional Formatting for more then 3 conditions
Microsoft Excel as many of you know, has a conditional formatting tool. This tool allows you to have up to three conditions on a single cell, or a range of cells.
This tool does work well, except when you wish to see a greater depth of three conditions.
The following macro was created to specifically deal with two variable sensitivity analysis tables. This isn't a re-usable macro persay; you'll have to change the conditions in the macro for each two variable sensitivity table you use it on.
If you are just here for the macro's, they are available here: | Macro for Conditional Formatting
I'm not going to explain why the macro works like it does (if you read the code, it's commented and programming 101 stuff). Instead, lets get down to the how to use these macro's.
The Tutorial
I've created a test excel spreadsheet for download that has the macro's already included, as well as a model setup for testing. This will be the file I use in the following tutorial. Download: | Macro/Model Test File
First, let's look at our data table in the example file:
Wait a minute you say, what's this warning? Depending on your macro security settings, you may have to enable macro's for the file to be able to use them. Click "Enable Macro's" to continue.
Okay finally we have our example model on screen, which looks like this:
Simply select the entire data table (but not the label row or column...otherwise those end formatted as well)
Now, access the macro menu by either clicking ALT+F8 or by going to Tools->Macro->Macro's. The screen below should appear:
You will should see one macro (you may see more depending on if you have other macro's installed). The WorksheetChange macro simply loops through all the cells in the selected range, and based on the Case statements in the macro, changes the background color.
Run WorksheetChange by selecting "WorksheetChange" and clicking "Run". If done correctly, you should see the result below:
That's it! Again please note, this is what you would call a hard-coded example. While the concept of the macro will work on most any table, you would need to change the Case statements to add/subtract how many conditions you want, and what values to test for.
