Conditonal format a because of values in another cell [Solved/Closed]

Report
-
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
-
Hello,

i am trying to figure out how i can format a cell to show green when above a figure and red when below the figure simple i know but here comes the tricky bit i need to format so that if the cell value is below target but adjacent cell is above target then to put it green.

if B3 is =>50 then green
if b3 is <50 then red
if B3 is <50 but cell c3 is =>1500 then green
if b3 is <50 but cell c3 is <1500 then red

Please help am struggling


1 reply

Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
135
Hi Watson,

I got this to work by creating 3 rules for cell B3. Try this:

1. Select the cell B3.
2. Click Conditional Formatting from the top ribbon.
3. Select New Rule.
4. Click "Format only cells that contain".
5. In the "Format only cells" section in the 1st drop down box choose "Cell Value"
6. In the "Format only cells" section in the 2nd drop down box choose "greater or equal to"
7. In the "Format only cells" section in the 3rd drop down box type
=50

8. Click the [Format] button followed by the "fill" tab and select a shade of green you like.
9. Click the [OK] button and again click the [OK] button.

10. Select New Rule.
12. Click "Format only cells that contain".
13. In the "Format only cells" section in the 1st drop down box choose "Cell Value"
14. In the "Format only cells" section in the 2nd drop down box choose "less than"
15. In the "Format only cells" section in the 3rd drop down box type
=50

16. Click the [Format] button followed by the "fill" tab and select a shade of red you like.
17. Click the [OK] button and again click the [OK] button.

18. Select New Rule.
19. Click "Use a formula to determine which cells to format".
20 In the "Format values where this formula is true" section type
=$C$3>1500=TRUE

21. Click the [Format] button followed by the "fill" tab and select a shade of green you like.
22. Click the [OK] button and again click the [OK] button.

23. Finally put a tick in the "Stop if true" boxes at the end of each line and then click the [OK] button.

Hopefully you will now have what you need. Please let me know how you get on.
hello thank you very much for that I gave up trying to put more than 2 conditional formats because it would come up with an error message but what you put there worked.

Thanks a bunch its furthered my understanding of conditional formatting to :)
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
135
Glad it worked for you.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!