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
• 27,940 points
436 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
• 51,600 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 4 days ago in Others by narikkadan
• 51,600 points
34 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 51,600 points
185 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 51,600 points
103 views
0 votes
0 answers

Merge rows based on value (pandas to excel - xlsxwriter)

I'm attempting to use xlsxwriter to output ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 27,940 points
575 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
• 51,600 points
116 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
• 51,600 points
150 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
• 51,600 points
265 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
• 51,600 points
121 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
• 51,600 points
154 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
• 51,600 points
136 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