Macro using dates

[Colleen]

I would like to write a macro that will make the
background color of the cells change depending on what
date is in the individual cell. For example, a cell that
shows a date that occurs this week will be in yellow, a
date that will occur in the future will appear green, etc.
What makes this challenging is that I want to make it so
that the macro will continually change as the date
changes, so that it does not need to be re-written on a
weekly basis.

[Myrna Larson]

If you have a default color plus at most three other options, you can use conditional formatting
for this.

The formula for a future date would be

=A1>TODAY()

The formula for a date that is in the current calendar week (beginning with Sunday) is

=A1>(TODAY()-WEEKDAY(TODAY()))

You must enter the conditions in the correct order: the first one to return TRUE is the one that
Excel uses. If you want dates that are in the current week but after today to colored as future
dates, the condition for future dates must be entered first, the condition for dates this week
2nd.

If you want all dates in the current week, past, present, and future, to be yellow, you need to
enter this as the first condition

=AND(A1>(TODAY()-WEEKDAY(TODAY())),A1<=(TODAY()-WEEKDAY(TODAY())+7))

and

=A!>TODAY()

as the 2nd condition.