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

  • Thread starter Thread starter Mike Barnard (at work)
  • Start date Start date
M

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.
 
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.
Click to expand...

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
 
How do you tell if training is due?
Click to expand...

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.
Click to expand...

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.
Click to expand...
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
Click to expand...
BUT you have more than three conditions -- you want some of the columns to be
clear.
Click to expand...
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
Click to expand...

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.
Click to expand...

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