Setting cells to change colors as a date approaches

[lilkel31]

I hope I am posting this in the right section. I have programmed with Excel
macros and the visual basic editor, but it has been a while and I don't have
a good solid list of commands to use.

I have a spreadsheet in which I track certain events, some that need to be
completed annually, some semi-annually, and some quarterly. I would like to
set up a macro that would allow for the cells in each group to change color
as they approach the due date. Is this possible? I would like it to be sort
of a reminder to me that the event needs to be done if I haven't put a
"completed" date in the cell yet.

Let me give a more specific example--I have the sheet set up basically like
a list, with the events grouped by their due timeframe (ie. all quarterly
events are listed in rows). I have the cells all formatted already to accept
the date and when I complete a certain event, I put the date it was
completed. What I would like to do it format the cells in each grouping
(annual, semi-annual, quarterly) to change color if I open the sheet and the
event is not marked as completed and the due period is near. I know this
explanation does not seem clear as I read it, but hopefully you understand
what I'm asking. If I open the sheet on Feb.6 and the end of the quarter is
Feb. 13, I want the cell to change color to remind me that I need to complete
those events that need to be done by Feb. 13.
Can this even be done? I am looking at conditional formatting, but am not
sure what commands to use to tell it what I want it to do. And, if this
procedure is too deep for conditional formatting, I am wondering the commands
(arguments, etc.) to use to accomplish it in VB.
Thanks in advance!

 

[akphidelt]

If you are using 3 conditions or less you can easily do this with Conditional
Formatting. If using more then 3 conditions you can use a Select Case
statement in VBA.

However the best advice I can give is to use a cell and input the formula
=Today()

For example purposes we will use A1 as =Today()

So select the area of conditional formatting you want and type in

Cell Value is between

=$A$1 and
=$A$1+7

This would highlight any cell that is between Today and 7 days ahead of today.
You can play with this as much as you want to get the conditions to meet
whatever you want.

 

[lilkel31]

I understand part of what you're saying, but I don't think conditional
formatting will work because I want the formatting to be so specific. I have
been playing with conditional formatting and see how it works. I understand
that I need to put the "today()" command for it to know what the current day
is, but I think I need to know specific macro command arguments to tell it
what I want. The cells are blank until I complete the event. Once I
complete the event, I enter the date the event was completed. What I would
like is for the cells that are blank a week or two before the due date to
turn red when I open the sheet. So, for instance, the cells in range F8:H21
are all things that need completed quarterly by March 31, end of the first
quarter. If I open the sheet on March 15th (or so) and there are events that
are not completed, I want to have them show me they are due by being red.
From what I've seen in the conditional formatting, this request is too
complicated. I understand what you are saying that I can have it change the
color out 7 or 14 days ahead of the current date, but can I do it backward
from a set end date?