Conditonal format a because of values in another cell

Solved/Closed
Watson - Mar 4, 2016 at 11:41 AM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Mar 7, 2016 at 09:34 AM
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


Related:

1 response

BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Mar 4, 2016 at 08:30 PM
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.
0
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 :)
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Mar 7, 2016 at 09:34 AM
Glad it worked for you.
0