Forum Discussion

dpearson112's avatar
dpearson112
Copper Contributor
Feb 16, 2022
Solved

Range comparison for conditional formatting

I can't seem to find anything that pertains to conditional formatting that changes a less than value into a different color, that is comparing a range of numbers of a different column. So like column...
  • mtarler's avatar
    Feb 16, 2022
    you need to create "New Rule" then select "Use a formula to determine which cells to format"
    Then in the box you create a relative equation based on the upper left most cell that will return TRUE when you want that formatting to be applied.
    So in your example you highlight all of column B and C and select new rule and type in the box:
    =$C1<$B1
    In case you don't know the $ is used to make that part of the reference ABSOLUTE so it will always check if the value in column C is < a value in column B but since the number 1 is NOT preceded by a $ that means as Excel looks at row 2 it will look at C2<B2 and then at row 3 it will evaluate C3<B3 and so on.

Resources