To format a cell based on the value of another cell follow these steps:
1. Select the cell you want to format, “A1″ in this example.
2. In the Home tab, Styles group, click on Conditional Formatting.
3. Select New Rule.
4. The New Formatting Rule window will open.
5. Click Use a formula to determine which cells to format.
6. The New Formatting Rule window will now look like this:
7. In the edit box labeled “Format values where this formula is true“, enter “=” followed by a formula referencing the other cell.
In this example the formula: “=B1<0″ is entered.
8. Click the Format button. The Format Cells window will open.
9. Specify the formatting you want. In this example I changed the cell color to “RED”.
10. Click OK to close The Format Cells window.
11. Click OK to close The New Formatting Rule window.
To summarize this example: cell A1 will have a RED color when cell B1 value is less than zero.