Conditional Formatting for any date within the following week

[PitBoo]

Hello,

I need to be able to highlight an entire row light blue if the REQUIRED DATE cell in that row (in this case it's cell L8) falls anywhere within next week.
So for today which is 6-17-19, any date entered between 6-24 thru 6-30 should trigger the row blue. Problem is I also need this to do this even if today's date is 6-21-19, this is why I can't just use the date+7 days thing.
So no matter what date it is I need it to turn blue for any date within the following week.

I tried to use the automatic CF function that you can pick under the Highlight Cells tab in the toolbar and it works great except in only turns the CELL blue not the entire row, even if I say it applies to the entire sheet.

This sheets applies to service area is =$B$8:$V$15000.

Thanks in advance.

[6StringJazzer]

Assuming your dates are all in column L, then you need to use a formula for a rule, not one of the built-in automatic ones.

This formula will do CF for the entire row with a date that is in the next week.

Please Login or Register  to view this content.

Apply to $B$8:$V$15000.

[PitBoo]

First of all thanks for the reply!

I tried your formula with the same results. I'll try to clarify a little.

If today is 6-18-19 and I type in 6-24-19 it should turn blue. If I type in 6-28-19 it should still be blue. But if I type in 7-1-19 (which is in the following calendar week) it should not turn anything. And that will change if today's date changes, If today's date is 6-21-19 everything will stay the same, next week will be 24th-28th. But if today's date is 6-28-19 the everything for 7-1-19-7-5-19 should be blue only.

Also your formula threw an error when copied as-is. I assumed you put an extra = in there in this bit "$L8<==TODAY()" so I just took one out.

[6StringJazzer]

Revised formula. It erroneously included the following Monday. Also thanks for noting the extra =.

Please Login or Register  to view this content.

[PitBoo]

That revised formula is returning a "False" for everything that is due next week now (6-24 thu 6-28) now.

It triggers "True" for anything for the week of 7-1, which is backwards.

The "True" should be for any dates next week and "False" for anything after next week.

Sorry this thing is such a pain, leave it to me to ask the weird questions.

[Aardigspook]

@Jeff
I think you deleted the < sign by accident as well. Should it not be this?

Please Login or Register  to view this content.

(I only looked at this quickly, so please tell me to butt out if I'm wrong and just causing confusion!)

[6StringJazzer]

@Jeff
I think you deleted the < sign by accident as well. Should it not be this?

Please Login or Register  to view this content.

(I only looked at this quickly, so please tell me to butt out if I'm wrong and just causing confusion!)

Please butt in because you are absolutely correct. smack.gif

[PitBoo]

That worked, thanks all!

[Aardigspook]

Glad we could help and thanks for the rep.

@Jeff, thanks for the rep too.