Conditional formatting based on another column
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:
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.
How this formula works
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:
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.
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.