Excel Conditional Formatting

Dave KIlby
Dave KIlby used Ask the Experts™
on
I have two conditional formatting questions

1 - I have a column with percentage values, If the percent is less than 92% but greater than 85% i want the background to be yellow, and then if it is less than 85% the background would be red.

2 - I have 2 columns B & C that have time values - I want to compare C to B and if C is great than B by 5 mins then the background would be red - example Column B = 9:00:00, Column C = 9:06:52.  Column C background would be red

How can I accomplish this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I suggest using a formula criteria to determine which cells to format.

For the first one, let's assume that cell D2 is the first cell that needs conditional formatting. You might use formulas like:
=D2<0.85    for the red format
=AND(D2>=0.85, D2<0.92)     for the yellow format
After entering the formula, click the Format button, go to the Fill tab and choose your color. Click OK twice.

For the second one, you might use a criteria like:
=AND(COUNT(B2:C2)=2, (C2-B2)>=TIME(0,5,0))     for the red format

Author

Commented:
Thank you - what if I wanted to check column D and E which are also time values, but I want to check if column E is 5 or more mins less than column D then format red
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you want to check if E-D is over 5 minutes, you would use:
=AND(COUNT(D2:E2)=2,(E2-D2)>=TIME(0,5,0))

If you want to check if both C-B and E-D are both over five minutes, you would use:
=AND(COUNT(B2:E2)=4, (C2-B2)>=TIME(0,5,0),(E2-D2)>=TIME(0,5,0))

If you want to check if either C-B or E-D are over five minutes, you would use:
=OR(AND(COUNT(B2:C2)=2, (C2-B2)>=TIME(0,5,0)), AND(COUNT(D2:E2)=2,(E2-D2)>=TIME(0,5,0)))
Rob HensonFinance Analyst

Commented:
For the percentage check you can use one of the standard conditional formatting setups and don't need to use the formula based check.

See screenshots below:
CF Rule 1CF Rule 2CF Result
Rob HensonFinance Analyst

Commented:
Also, are you aware of the Conditional Formatting where you can add icons to the cell rather than filling the cell with a colour.
CF Icons

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial