Excel’s conditional formatting is much more useful than it initially seems to be. You don’t need to base the formatting you set upon the value of the cell you’re formatting; you can format one cell based on the value of a different cell.
In fact, you can base the formatting on formulas and not just on the cell’s simple value. You can even use functions like IF, AND, OR, and others in the formula, referencing multiple cells as necessary, to determine your formatting.
When using conditional formatting on a range of cells, be sure to build your formulas carefully using the proper absolute and/or relative addressing mode. Excel defaults to absolute mode when you click to select cells, so just hit F4 to toggle between the mixed absolute row and column modes, and the relative mode.
Finally, don’t forget that you can set up multiple condition rules and formats.
