Newbie here - colour a cell depending on the date and contents?

[Mike Barnard (at work)]

Hi all.

I'm new to the group and do very little with excel. I'd like to do
the following just to to make my life easier at work. I know about
conditional formatting, but what I want to do is a bit too complex for
that, I think. Therefore I must need VBA but I've never used it
before. Any pointers please?

I am doing a sheet which will keep track of the training needs of a
group of people. Some training needs to be done once every 6 months,
some once a year and some just once every 2 years.

So I have names on the left in the rows and each column refers to an
item of training. Item "A" may have one column because it's two
yearly, item "B" two columns for annually and item "C" four columns
because it's 6 monthly. All I'm doing is putting a "1" in a cell if
the training has been done.

I want....
- Columns that are not yet due to be greyed out.
- Annual columns to be greyed if current date is not within the year.
- 2 year columns to always be clear.
- Cells that are done to be green. (I've done this with conditional
formatting).
- Cells that are due this period to be yellow
- Cells that are missed to be red.

I guess I need an if then...

IF [contents of current cell] = 1
THEN cell goes green
ELSE IF this column is not due
Then grey it out

Hmmm... how to nest the rest of it neatly and properly?

Can anyone help me get this working please? Not the full code, but
ideas of how to get into the code, best ways of nesting, how to refer
to the dates?

Not much to ask I know!

Thanks.

Mike Barnard
Worthing, UK.

 

[Frank Kabel]

Hi
you may try the following addin as you require more than 3 conditions:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[Ron Rosenfeld]

Hi all.

I'm new to the group and do very little with excel. I'd like to do
the following just to to make my life easier at work. I know about
conditional formatting, but what I want to do is a bit too complex for
that, I think. Therefore I must need VBA but I've never used it
before. Any pointers please?

I am doing a sheet which will keep track of the training needs of a
group of people. Some training needs to be done once every 6 months,
some once a year and some just once every 2 years.

So I have names on the left in the rows and each column refers to an
item of training. Item "A" may have one column because it's two
yearly, item "B" two columns for annually and item "C" four columns
because it's 6 monthly. All I'm doing is putting a "1" in a cell if
the training has been done.

I want....
- Columns that are not yet due to be greyed out.
- Annual columns to be greyed if current date is not within the year.
- 2 year columns to always be clear.
- Cells that are done to be green. (I've done this with conditional
formatting).
- Cells that are due this period to be yellow
- Cells that are missed to be red.

I guess I need an if then...

IF [contents of current cell] = 1
THEN cell goes green
ELSE IF this column is not due
Then grey it out

Hmmm... how to nest the rest of it neatly and properly?

Can anyone help me get this working please? Not the full code, but
ideas of how to get into the code, best ways of nesting, how to refer
to the dates?

Not much to ask I know!

Thanks.

Mike Barnard
Worthing, UK.

How do you tell if training is due?

Perhaps you have a row under the training item with a due date? But it might
be different for each person, unless they all get trained on the same day.

In any event, you may have up to three conditional formatting schemes (plus
your base)

So let's leave the base as a grayed out cell.

Then CF#1 could be Cell Value Is equal to 1 | Format green
CF#2 could be Formula Is: =today()>due_date | Format red
CF#3 could be Formula Is:
=AND(today()<=due_date,today()>=(due_date-whatever)) | Format yellow


BUT you have more than three conditions -- you want some of the columns to be
clear.

So unless someone comes up with something more clever (or you reduce your
required conditions), you will need a VBA solution. Using event macros and the
Select Case construct, you can have as many conditions as you require. This
has been discussed in this NG in the past.


--ron

 

[Mike Barnard (at work)]

Hi
you may try the following addin as you require more than 3 conditions:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

Thanks, I'll look at that, but was more interested in the VB side.
But hey, what works, works.

 

[Mike Barnard (at work)]

How do you tell if training is due?

It's just listed in a book at the moment. For example, once every
period of 104 weeks each person must identify all types of fire
extinguishers, and select the correct extinguisher to meet the needs
of a situation, and do this, and do that, etc. All of which means
they have done thier continuation training on portable firefighting
equipment. Then the person can have his box marked, if you see what I
mean.

But at the moment I have it recorded on paper,and I have to trawl
through all these books to see whats due etc. Arggh.

Perhaps you have a row under the training item with a due date? But it might
be different for each person, unless they all get trained on the same day.

As I say, each is reciorded seperately.

In any event, you may have up to three conditional formatting schemes (plus
your base)
Yes.

So let's leave the base as a grayed out cell.

Then CF#1 could be Cell Value Is equal to 1 | Format green
CF#2 could be Formula Is: =today()>due_date | Format red
CF#3 could be Formula Is:
=AND(today()<=due_date,today()>=(due_date-whatever)) | Format yellow

BUT you have more than three conditions -- you want some of the columns to be
clear.

So unless someone comes up with something more clever (or you reduce your
required conditions), you will need a VBA solution. Using event macros and the

This is what I expected, and am looking for.

Select Case construct, you can have as many conditions as you require. This
has been discussed in this NG in the past.

Aha, an answer. I shall look this up and proceed from there. Many
thanks.