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?

Oct 10, 2022 in Others 1,335 views

## 1 answer to this question.

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.

• 63,420 points

## Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

## Conditional format based on external cell value

Use a rule with the formula =\$Q6="Yes" and apply ...READ MORE

## Conditional formatting based on value with offset

Apart than the altered cell, Conditional Formatting ...READ MORE

## Excel automatically converting 7 digit CAS number to another number (date?)

Looks like you could use: The formula in D2: =SUBSTITUTE(F ...READ MORE

## Thousand and million formatting for negative numbers (excel/ googlesheets)

Its not possible. What you could do is ...READ MORE

## Excel Issue- custom format for Turkish currency

What about: #.##0,00 [\$₺-tr-TR] Where #.##0,00 € would do for ...READ MORE

## How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE