Both VBA and conditional formatting will work, though the later is easier.

Steps
------
1. Mark the whole column, or the range that you want to apply the format.
2. Select menu Format/Conditional formatting. A dialog will popup.
3. You will start will 'condition 1', select/enter the followings:
- Cell Value is
- between
- =TODAY()
- =TODAY()-7
4. Click the "Format" button. 'Format cell' dialog will pop up.
5. Chage 'color' to 'red'. Click 'OK'.

6. Click the 'Add' button to add another condition.
7. Repeat step 3-5; values to enter are
- Cell Value is
- between
- =TODAY()-8
- =TODAY()-14
8. Set color to 'yellow'

9. Click the 'Add' button to add another condition.
10.Repeat step 3-5; values to enter are
- Cell Value is
- between
- =TODAY()-15
- =TODAY()-21
11. Set color to 'green'

12.Click 'Ok' to close the 'Formatting' dialog.

That's all.


HTH -- arunkhemlai






Column A contains a list of date values (e.g. 11/04/2005).

I'm looking for a way to change the cell color based on the following
criteria ...

If the date falls earlier than today but no earlier than a week before
today then cell color to be red.

If the date falls earlier than today but no earlier than two weeks before
today then cell color to be yellow.

If the date falls earlier than today but no earlier than three weeks before
today then cell color to be green.

Can anyone offer a solution to this please? Should I be looking at
conditional formatting or should I use VBA?