We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Conditional Formatting - Update to question

GordonMasson
GordonMasson asked
on
Medium Priority
240 Views
Last Modified: 2012-05-11
I had some help with conditional formatting but i still seem to have an error that i hadn’t been aware of.
Please see attached sheet which shows the error.

Also can someone explain what the original formatting formatting actually refers to......and any changes required to make it work 100%
Looking at the format you used can i just make sure i understand what it means please:

=AND(H5="",G5>TODAY(),G5>0)
I take it =AND means that all the terms in the bracket are checked so:

H5=""  If H5 is blank  AND ALSO
G5>TODAY()  the date in cell G5 is bigger than today’s date AND ALSO
G5>0 the date in G5 is bigger than 0.....not quite sure why this one is included?

THEN Format the cell as shown.
So i think that’s all ok but i am not sure about the G5>0 bit

The other thing i don’t understand is that if i look at the formatting of say cell G20, it still uses
=AND(H5="",G5>TODAY(),G5>0)

Thanks

Copy-of-Format.xlsx
Comment
Watch Question

Commented:
The background of that cell (G6) is set to red by default so even though the conditional formatting doesn't apply its red anyway, set it no fill and the rules will work.

The G5>0 is probably just to make sure that text isn't entered.

The reason the rule looks the same for G20 is that it applies to a range, you can edit this rang in the manage rules window.

Author

Commented:
Ah ... stupid of me, i missed that!

And is this bit?

Looking at the format you used can i just make sure i understand what it means please:

=AND(H5="",G5>TODAY(),G5>0)
I take it =AND means that all the terms in the bracket are checked so:

H5=""  If H5 is blank  AND ALSO
G5>TODAY()  the date in cell G5 is bigger than today’s date AND ALSO
G5>0 the date in G5 is bigger than 0.....not quite sure why this one is included?

THEN Format the cell as shown.
So i think that’s all ok but i am not sure about the G5>0 bit

The other thing i don’t understand is that if i look at the formatting of say cell G20, it still uses
=AND(H5="",G5>TODAY(),G5>0)
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
The G5>0 is because cell G5 may or may not be populated based on the sample data he gave me, and he asked that it go through about 600 rows, i applied it from G5:G600 and it only formats the cells that have data......

Brandon
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks guys...

Thats me happy.
Not sure about the points...i never am when there are multiple answers.

ConUladh 400
KnutsonBM 50
barryhoudini 50

Does that seem fair?
you can leave me out, i was just adding explanation to the answer i gave before, no worries there. :)

Brandon

Author

Commented:
Thanks for your help guys
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.