Conditional Formatting - Update to question

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
GordonMassonAsked:
Who is Participating?
 
ConUladhConnect With a Mentor Commented:
=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?

Correct


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

To stop a non-valid text entry confusing the matter.

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)

When you go to Conditional Formatting -> Manage rules you will see that formatting applies to a range so the references in the rule are to the first entry in that range, you can modify the range in that screen as well.


0
 
ConUladhCommented:
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.
0
 
GordonMassonAuthor 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)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
KnutsonBMCommented:
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
0
 
barry houdiniConnect With a Mentor Commented:
There isn't much point in having both G5>TODAY() and G5>0, if the first is satisfied then the second must be, so it's redundant here - if it's possible that G5 could be text and you don't want to trigger the conditional formatting in that case then you need to check explicitly that G5 is a number (i.e. a date) so in that case you might want to use:

=AND(H5="",G5>TODAY(),ISNUMBER(G5))

In Excel 2007 the condition doesn't explicitly show the change whichever cell you look at (unlike Excel 2003 where the condition would look different, so you'd have =AND(H20="",G20>TODAY(),G20>0) in G20). It work's the same, though, you just have to "imagine" what the condition will be for that cell/row!, as long as you don't use $ signs then the condition will adjust row by row

regards, barry
0
 
GordonMassonAuthor 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?
0
 
KnutsonBMCommented:
you can leave me out, i was just adding explanation to the answer i gave before, no worries there. :)

Brandon
0
 
GordonMassonAuthor Commented:
Thanks for your help guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.