Conditional Formatting Using If Statement On Opening Of Workbook

[Bill Foley]

Hey gang,

I was wondering if anyone had a solution for this EXCEL query:

I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12 months),
bi-annual (24 month), 6-month, or even 2-month re-training.

What I am trying to accomplish is to automatically shade all cells for dates
that fall within one month of these periodicities based on the computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are more
than 11 months from today's date (A1000 minus 1), they are within one month
of needing to be re-trained and I want the cell shading to be a light
yellow.

Can this be done using Conditional Formatting without VBA or do I need
something to be run based on an AutoOpen macro?

TIA!

 

[Guest]

You should be able to do that with conditional formatting. Set the condition
to read 'cell value is less than' and the value to compare to is
=TODAY()-$A$1000, with the value in $A$1000 set to the number of days allowed
between retraining less the number of days advance notice desired. You could
set it up so that you've got multiple conditions with different periods to
create shades showing greater urgency, but be sure that you test for the
'most urgent' first.

 

[Bob Phillips]

Bill,

You should be able to do it without VBA .

When you say periodicity, will you enter a date like 1st Aug 2000, and need
it coloured in Jul 2001, Jul 2002, Jul 2003, etc., or is it a once-ff?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bill Foley]

Thanks, bpeltzer! I was hoping to figure out exactly one month from that
day, but this works just fine. Close enough for government work! HA!

I appreciate it!

 

[Bill Foley]

Actually bpeltzer sort of gave me a solution, but let me explain further in
case of another way.

Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be shaded
when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is already
past that date, it still needs to be shaded until a new date is entered when
re-training is done. Since there any number of rows and dates, I need it to
look at each cell in the range and shade it if it is older than 11 months
from today's date.

The kicker (that I have not addressed from the previous solution) is I also
have several cells in a range from A1 - A500 that do not have dates in them
since these folks did not ever get trained on that topic. When I run the
"=TODAY()-$A$1000" condition, blank cells are also shaded. I know how to do
a conditional formula to only run a formula if a cell has a value in it, but
wasn't sure if that could be done with conditional formatting also.

THANKS!

 

[Bob Phillips]

Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be shaded
when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is already
past that date, it still needs to be shaded until a new date is entered when
re-training is done. Since there any number of rows and dates, I need it to
look at each cell in the range and shade it if it is older than 11 months
from today's date.

My reading of the periodicity made me wonder if you would put a date in, and
then you wanted it highlighted every said period after that date. The fact
that you are want it to saty shaded until it expires and will then update
the date makes it significantly easier.

You can use the technique given, but still use a number of months as you
originally asked with a formula of

=DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))

"=TODAY()-$A$1000" condition, blank cells are also shaded. I know how to do
a conditional formula to only run a formula if a cell has a value in it, but
wasn't sure if that could be done with conditional formatting also.

This can be handled, albeit with a more complex formula. To do this, and
apply to all cells and all sheets

On the first sheet, select all the cells that will have dates on one
worksheet (assuming starting at A2)
Group from that sheet to the last (hold shift and click the final sheet tab)
Format>Conditional Formatting
Change conditions 1 to Formula Is
Add this formula
=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
Add the format
OK

 

[Bill Foley]

Sure enough worked as well. THANKS!

One last thing - Any idea how to skip blank cells in a range (or have an IF
statement that only does the conditional formatting if the cell has a date
in it)?

 

[Roger Govier]

Hi Bill

You could use an AND() statement wrapped around Bob's solution.
=AND(NOT(ISBLANK(A$1000)),DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY())))

Regards

Roger Govier

 

[Bob Phillips]

Bill,

That last formula I gave you, with the way to implement it, should do just
that.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bill Foley]

I tried it but all the blank cells in the range I applied the conditional
formatting got shaded as well. I will continue to look into it.

THANKS!

 

[Bob Phillips]

Bill,

Do you want to post me the workbook?

Bob

I tried it but all the blank cells in the range I applied the conditional
formatting got shaded as well. I will continue to look into it.

THANKS!

 

[Bill Foley]

If you want, you can send anything you have directly to me at:

pttinc at itexas dot net

THANKS!