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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mike Barnard (at work) (junk_at_thunderin.co.uk)
Date: 11/23/04 

Date: Tue, 23 Nov 2004 19:12:05 +0000

On Tue, 23 Nov 2004 07:00:37 -0500, Ron Rosenfeld
<ronrosenfeld@nospam.org> wrote:

>>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.

>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.

>
>--ron

Quantcast