Forum Discussion
Noortje Vollenberg
Oct 06, 2017Copper Contributor
Conditional Formatting - formula for adjacent cells
Hello,
Sorry for posting again but I got no response on my original post, so here I am trying again. If anyone could help me that would be great! I need to set up conditional formatting for a column where the cell turns red or green based on the information in the cell to their left.
I have the following information in an Excel sheet:
November | |
Expected | Actual |
0 | 1 |
20 | 19 |
60 | 40 |
20 | 20 |
Now I want to set conditional formatting for column B "Actual". If:
- B3 >= A3 then B3 is green;
- B3 < A3 then B3 is red.
And so on for the following rows: B4 >= A4, B4 is green / B4 < A4, B4 is red, etc. etc.
I'm not sure how to set this up without having to set conditional formatting for every single cell. And since I have many more of these collumns and rows I really do not want to do that. Is there a formula that I could use?
Thanks in advance for your help!
Hi Noortje,
1) Format in red column B
2) Add rule for "green" condition as
3) Apply it to column B
Result is
Just be careful with absolute and relative regferences in the rule formula
Hi Noortje,
1) Format in red column B
2) Add rule for "green" condition as
3) Apply it to column B
Result is
Just be careful with absolute and relative regferences in the rule formula
- garethsnaimCopper Contributor
Hi sorry dredged this up from a search as I need the same, it sort of works but where the numbers are the same it gets coloured but I need it to remain black if there is no change?
- Noortje VollenbergCopper Contributor
Hi Sergei,
I'm not too familiar with absolute and relative references so I didn't even think of that. It's working now!
Thanks so much for you help.