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

Hi,

I am trying to create a tasker for work. I need the task to highlight in red if the suspense date has already passed. (i.e., if the task was due 29 jun and today is 9 jul, i need it to highlight red. 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.

Does anyone know how to do this?

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

Sorry if this confuses anyone...

Thanks for your help.

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

... 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(B70,B7<TODAY())
and Format with red highlight.

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

(Testing B70 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.