Change Color of Cell based on Date and today's date.

[SammiJojo412]

Hi,

I am trying to create a tasker for work. I need the task to highlight i
red if the suspense date has already passed. (i.e., if the task was du
29 jun and today is 9 jul, i need it to highlight red. If the task i
due between todays date and 6 weeks out, I need it to highlight yellow
If the task is due more than 6 weeks out, I need it to highlight Green


Does anyone know how to do this?

Also when in puting a formula, is there a way that the formula ca
automatically change for that row number... for example if the formul
is =b7-b3, when i move to b8 is there a way to write the formula so
don't have to individually type each row number in the formula?

Sorry if this confuses anyone...

Thanks for your help

 

[zvkmpw]

... I need the task to highlight in

red if the suspense date has already passed....
If the task is
due between todays date and 6 weeks out, I need it to highlight yellow.
If the task is due more than 6 weeks out, I need it to highlight Green.

Also when in puting a formula, is there a way that the formula can
automatically change for that row number

Here's one way with Excel 2003.

First, highlight all the date cells of interest in green. Then add conditional formatting for the other two colors.

Specifically, select B7 and use:
Format > Conditional Formatting

For Condition 1 use
Formula Is >
=AND(B7>0,B7<TODAY())
and Format with red highlight.

For Condition 2 use
Formula Is >
=AND(B7>0,B7<TODAY()+42)
and Format with yellow highlight.

(Testing B7>0 is to prevent empty cells from becoming red.)

To apply the conditional formatting to other cells, use the format painter button. It's the button that looks like a paint brush. The conditional formulas adjust for the row and column where the format is painted.