Report

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

Ask a question Watson - Latest answer 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 877Posts Saturday January 17, 2015Registration date ModeratorStatus August 31, 2016 Last seen - Mar 7, 2016 09:34AM
Glad it worked for you.
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!