Update to Conditional formatting Question

Hello Gordon

Your conditional formatting is only applied when column D is empty......so I'm not sure what you mean by looking at C instead of D. Do you mean that you only want the conditional formatting to be applied if both C and D are empty?

regards, barry

Hi Barry
In some cases column D may not have a value in it.
If column D DOES have a value then i want to format column E based on those values....as per the demo sheet attached above.
If column D DOES NOT have a value in it then i want to format column E based on the value in column C

Sorry if i am not making this clear but i hope this helps.

Thanks

Gordon

Hi Gordon,

I don't fully understand either.

At the moment the conditional formatting only kicks in if the cell in D is empty.

Are you saying that you only want the conditional formatting to kick in if C is empty as well? If so just add C5 = "" to each rule.

Regards
Stephen

Sorry guys, I think I am confusing myself now!!

See attached sheet which should hopefully explain what I am trying to achieve… the test sheet I had earlier had columns mixed up in order but this is right

Test-Sheet.xlsx

Hello Gordon, you say:

"If column D DOES have a value then i want to format column E based on those values....as per the demo sheet attached above"

but your conditional formatting isn't using the date in column D.......it's only applied if column D is empty.

Can you just explain what you want to do - preferably without reference to any existing formulas - and which cells should be which colour given that - currently E5, E6 and E8 are red and E13 and E14 are green, should that be different?

regards, barry

Sorry, I posted that last response before I saw yours...

Are you sure F6 should be green. There's a date in H6 so shouldn't F6 remain unformatted? If so try this for green condition

=AND(H1="",OR(G1>TODAY(),AND(G1="",F1>TODAY())))

and this for red

=AND(H1="",OR(AND(G1<TODAY(),ISNUMBER(G1)),AND(G1="",F1<TODAY(),ISNUMBER(F1))))

see attached

regards, barry
26975647.xlsx

Sorry for the confusion.

I have 3 columns I am interested in and for each row in the sheet:
The first column always has a date in it.
The second column may have a date in it.
The third column may have a date in it.

What I want to achieve is to add a colour fill to the first column but only If the third column does not have a date in it.
If the third column does have a date in it then the first column should retains its white / clear colour fill. (it doesn’t matter what this date is, as long as there is a date in the third column then the first column remains white / clear)

So assuming there is no date in the cell in the third column:
If there is a date in the second column which is AFTER today’s date then the first column should be formatted with green colour fill….. i.e not yet due
If there is a date in the second column which is BEFORE today’s date then the first column should be formatted with red colour fill…..i.e overdue

If there is NOT a date in the second column then the first column should be formatted based on today’s date.
If the date in the first column is AFTER today’s date then the first column should be formatted with green colour fill. ….. i.e not yet due
If the date in the first column is BEFORE today’s date then the first column should be formatted with red colour fill. …..i.e overdue

I hope this helps explain things. Sorry if its not clear.

Yes that looks right thanks barry...just checking in more detail now and of course you were right about F6.

Actually there's a simpler way......

If you use a LOOKUP formula that will return G1 if that has a date but F1 otherwise, so your green condition can be

=AND(H1="",LOOKUP(10^10,F1:G1)>TODAY())

and red identical except with > changed to <

that should give the same results as my previous suggestion

regards, barry

Thanks for your help Barry

Cheers
Gordon