cell automatically turns colour??

[Guest]

Dear Excel users,
I have a spreadsheet containing names and dates......i would like to
know if there;s any formula for the below:

Let's say.......2 weeks before a due date....the cell with a name on it
will turn "orange" colour.....2 week after.... on the due date....the cell
will auto change to "red" colour.....
we would need this notification very urgently ......
Pls advice me....Thank you very much....

 

[Guest]

see conditional formatting - set up a formual comapring today() to the due date

 

[Guest]

Sorry....im not expert in Excel....so would you mind teaching me step by step
in the formula?? please....

.....

 

[Biff]

Hi!

Assume cell A1 contains the name and cell B1 contains the due date.

Select cell A1
Goto Format>Conditional Formatting
Condition 1
From the drop down select Formula Is
In the box to the right enter this formula: =AND(B1<>"",B1<=TODAY())
Click the Format button
Set the fill color to RED
Click OK

Click the Add>> button

Condition 2
Formula IS: =AND(B1<>"",B1-14<=TODAY())
Click the Format button
Set the fill color to orange
OK out

14 days from the due date cell A1 will turn orange and stay orange until the
due date. On the due date cell A1 will turn red and stay that way.

Biff

 

[Guest]

Dear Biff....
I have tried your method...but then..i think there's still some slight
error....when i change the due date to 2 weeks before it...it didnt change
orange...but stayed red all the way....eventhough i change it to 3 weeks
before the due date....
Any idea? do reply asap....Thank you very much

"

 

[Guest]

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??


"

 

[Biff]

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 Insert>Name>Define
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