I answered your earlier post where you asked for Column H to be highlighted
when the due date became past due.
If you wish, you can combine the conditions, where 7 days before the due
date the cell is one color, and when it becomes past due it becomes another
color.
Here's a copy of the first post:
Say your dates start in G2 and go to G100.
Select H2 to H100 and then:
<Format> <Conditional Format>
Change "Cell Value Is" to "Formula Is", and enter this formula:
=AND(G2<>"",G2+90<=TODAY())
Now, click on "Format" and choose a Pattern color to your liking.
Then <OK> <OK>.
And you should have what you're looking for.
However, you may get tired of looking at the colored cells in Column H.
You could remove the date from Column G.
But, if the date must remain, you might put a time limit on the formatting
of Column H.
Try this formula instead of the first one:
=AND(G1<>"",G1+90<=TODAY(),G1+120>TODAY())
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
NOW, to add a format for highlighting the cells 7 days prior to becoming
due, *Don't* hit <OK> twice after selecting your first format.
Hit it once, then click on "ADD" to add a second condition.
Again, click "Formula Is", and enter this formula:
=AND(G1<>"",TODAY()>=G1+83,TODAY()<=G1+90)
Click on "Format" and add a second, contrasting Pattern color.
Now, 7 days prior to the due date the cells should become one color, on the
due date change to another color, and if you chose the time limit option,
revert to neutral after 30 days.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
