Excel Conditional Formatting based on Adjacent Cell Value

0 votes

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:

Expense Conditional Formatting

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.

enter image description here

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 by Kithuzzz
• 38,020 points
1,785 views

1 answer to this question.

0 votes

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.

answered Oct 10, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

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

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

answered Jan 29, 2023 in Others by narikkadan
• 63,720 points
690 views
0 votes
1 answer

VBA Excel: Draw line between cells based on cell value

In accordance with your description and with ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,720 points
1,334 views
0 votes
1 answer

Conditional format based on external cell value

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

answered Feb 16, 2023 in Others by narikkadan
• 63,720 points
676 views
0 votes
1 answer

Conditional formatting based on value with offset

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

answered Apr 4, 2023 in Others by Kithuzzz
• 38,020 points
550 views
0 votes
1 answer

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

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

answered Sep 25, 2022 in Others by narikkadan
• 63,720 points
896 views
0 votes
1 answer

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

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

answered Sep 25, 2022 in Others by narikkadan
• 63,720 points
1,237 views
0 votes
1 answer

Excel Issue- custom format for Turkish currency

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

answered Oct 10, 2022 in Others by narikkadan
• 63,720 points
1,454 views
0 votes
1 answer

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

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

answered Oct 24, 2022 in Others by narikkadan
• 63,720 points
1,085 views
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,720 points
1,449 views
0 votes
1 answer

Excel Define a range based on a cell value

Let's say that cells A1, A2, A3, ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,720 points
3,206 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP