Excel Conditional Formatting based on Adjacent Cell Value

Question

In order to accomplish the following, I'm attempting to use conditional formatting in Excel on a range of cells based on the value of the next cell:

If a value in Column B (Actual Expense) exceeds its neighbouring value in Column C, it should be highlighted in red (Expected Expense). I have complied with several tutorials that instructed me to use conditional formatting by choosing Conditional Formatting>New Rules. Applying the criteria =$B4>$C4 would format the "Actual" cell red if it were greater than the "Expected" cell. Use a Formula to Determine Which Cells to Format. When I try to apply this to a group of cells, it doesn't work as effectively as it does for one cell.

I'd rather not make individual rules for every row. Is there a way to consolidate this into one rule?

Answer 1

The row number used in the formula should match the first row of data, therefore if you are applying this to the ("applies to") range $B$2:$B$5, it must be this formula. You need to remove the $ signs before the row numbers in the formula.

=$B2>$C2

Excel (implicitly) modifies the formula for each row in the range if you use that "relative" version rather than your "absolute" one, as if you were copying the formula down.