Conditional formatting based on another column

=$B1>$A1
To apply conditional formatting based on a value in another column, you can create a rule based on a simple formula. In the example shown, the formula used to apply conditional formatting to the range D5:D14 is:
=$D5>$C5
This highlights values in D5:D14 that are greater than C5:C14. Note that both references are mixed in order to lock the column but allow the row to change.
In this example, a conditional formatting rule highlights cells in the range D5:D14 when the value is greater than corresponding values in C5:C14. The formula used to create the rule is:
=$D5>$C5
The rule is applied to the entire range D5:G14. The formula uses the greater than operator (>) to evaluate each cell in D5:D14 against the corresponding cell in C5:C14. When the formula returns TRUE, the rule is triggered and the highlighting is applied.
Mixed references
The mixed references used in this formula ($D5, $C5) make this rule portable. You could use the same formula to highlight cells in B5:B14 instead of D5:D14, or even to highlight entire rows based on the same logic.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.