Forum Discussion
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 B set of Numbers, then Column C new numbers and if less than change to red. I feel like this is easy, but I can't seem to find a solution to this. Any help would be wonderful.
- 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.
2 Replies
- 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.- Thank you very much, this is exactly what I was looking for! I'm not what you would call an advance user, but i'm not new either. I still have a lot to learn, and it has been a lot of fun learning. Thanks for your help!
