Hi!

Is the due date in another worksheet in the same workbook(file), or, is the
due date in different workbook(file) ?

If the due date is in the same workbook(file) but on a different worksheet
then you can use the Indirect function to refer to that location. Something
like this:

=AND(INDIRECT("sheet2!B1")<"",INDIRECT("sheet2!B1 ")<=TODAY())

OR, you could give the cell that contains the due date a defined name:

Goto InsertNameDefine
Name: DueDate
Refers to: =Sheet2!$B$1

Then use that name in the formula:

=AND(DueDate<"",DueDate<=TODAY())

If the due date is in a different workbook(file) then you can't directly
reference that cell in conditional formatting. You would have to use a
helper cell to link(reference) to the due date. Then use the helper cell as
the reference for the conditional format.

For example:

Link cell C1 to the due date that is in the workbook(file) Test.xls sheet2
B1. If the file is closed include the path:

In C1: ='C:\FILES\[Test.xls]Sheet2'!B1

Then, you would just use cell C1(which now contains the due date) as the
reference for the conditional format.

Biff

"Kelly Lim" wrote in message
...
Sorry...i think i made a mistake...the formula does works...
but then if my due date is in another page( meaning i have a link to
another spreadsheet which only contains the due date inside ) ...how do i
make it works changing the colour ...when the date is the next
spreadsheet??


"