Excel Conditional Formatting based on Adjacent Cell Value

Question

I m trying to apply conditional formatting in Excel on a range of cells  based on the adjacent cell s value  to achieve something like this     The goal is to highlight values in Column B  Actual Expense  red if the value is greater than it s adjacent value in column C  Expected Expense   I ve followed a number of tutorials that said to apply conditional formatting by selecting Conditional Formatting New Rules Use a Formula to Determine Which Cells to Format then applying a rule   B4  C4 which would format the  Actual  cell red if it were greater than the  Expected  cell  This works great for one cell  but not so well when I attempt to apply this to a range of cells  I ve used the format painter as well as editing the rule to be applied over a range of cells  i e   B 2  B 5  as shown below but it only bases the formatting on the initial formula  if B4 is greater than C4  and everything undesirably turns red     I d rather not make individual rules for every row  Is there a way to consolidate this into one rule

User · Answer

I don t know if maybe it s a difference in Excel version but this question is 6 years old and the accepted answer didn t help me so this is what I figured out  Under Conditional Formatting  gt  Manage Rules   Make a new rule with  quot Use a formula to determine which cells to format quot  Make your rule  but put a dollar sign only in front of the letter   A2 lt  B2 Under  quot Applies to quot   Manually select the second column  It would not work for me if I changed the value in the box  it just kept snapping back to what was already there   so it looks like  B 2  B 100  assuming you have 100 rows   This worked for me in Excel 2016

User · Answer

You need to take out the   signs before the row numbers in the formula    and the row number used in the formula should correspond to the first row of data  so if you are applying this to the   applies to   range  B 2  B 5 it must be this formula    B2 gt  C2  by using that  relative  version rather than your  absolute  one Excel  implicitly  adjusts the formula for each row in the range  as if you were copying the formula down

[excel] (Excel) Conditional Formatting based on Adjacent Cell Value

Examples related to excel

Examples related to formatting

Examples related to conditional