Of course, in the chart, you are not limited to three conditional formats as you are in the worksheet (in Excel 2003 at least; the conditional formatting enhancements in Excel 2007 are mind-boggling). To format by value, see VBA Conditional Formatting of Charts by Value.
Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats.
Let’s see this in the below VLOOKUP example: There are many decent posts on why to consider using INDEX MATCH against the common VLOOKUP.
But before we go into the pros and cons let’s understand how the INDEX MATCH combo works.
There is a simple VBA procedure that will apply the colors from the designated range to the appropriate seriess, based on their names.