Report

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

Ask a question Watson - Last answered on Mar 7, 2016 09:34AM
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
See more 
Helpful
+0
moins plus
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.
watson- Mar 7, 2016 03:00AM
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 :)
Reply
BrianGreen 882Posts Saturday January 17, 2015Registration date ModeratorStatus November 27, 2016 Last seen - Mar 7, 2016 09:34AM
Glad it worked for you.
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!