Conditional formatting-Date range

[smack]

I am creating a spreadsheet which is a checklist for a shipping processs.

At the top I key in customer name and date the shipping process starts- let
say Feb/15/2010. I require a number of documents that prompt other portions
of the process. Therefore I need an indicator if I do not receive these
within 7 days of the start date ie:

Column 1 Column 2
Date received Date verified

If column 1 remains blank (no date entered) and is past the start date by 7
days I would like it to turn red.

NEXT if there is a date entered in that cell I need an additional cell to
turn another colour prompting the next step...confusing??

Any help would be greatly appreciated!!!

Thanks

 

[OssieMac]

From the limited information supplied, the problem here is whether to use
absolute addressing (with $ signs) or relative addressing in the formulas.

Assuming that the Feb/15/2010 is in cell A2 and then all cells in column A
need to refer to it then need to use absolute addressing like $A$1. Therefore
the following formula for column A from A3 down. Note A3 is relative but
absolute addressing required for $A$2 because you don't want that address
changing as the formula is applied to cells below A3.

=AND(ISBLANK(A3),TODAY()-$A$2>7)

Note when entering conditional format, you can just select one cell and
apply it and then Copy -> Paste Special -> Formats to the other cells.
Alternatively, you can select the range of cells to which the conditional
format is to apply and enter the formula as if it applies to the first cell
of the selection only and Excel looks after applying it to the remainder of
the selection.

Now if you want to apply conditional formatting to cells across the same row
as A3 when A3 contains a value the the following.

=ISBLANK($A3)=FALSE

Note that only the column is absolute here so you can apply the same formula
across the row and it will always refer to column A but as you copy the
format down, it will refer to the new row number.

Hope I have explained well enough but feel free to get back to me if you
have any further questions on it.