Change cell color dependin on date ...
 

Hi,

Looking for some help please ...

Column A contains a list of date values (e.g. 11/04/2005).

I'm looking for a way to change the cell color based on the following
criteria ...

If the date falls earlier than today but no earlier than a week before
today then cell color to be red.

If the date falls earlier than today but no earlier than two weeks before
today then cell color to be yellow.

If the date falls earlier than today but no earlier than three weeks before
today then cell color to be green.

Can anyone offer a solution to this please? Should I be looking at
conditional formatting or should I use VBA?

Many thanks.

--
T. Denford.

Both VBA and conditional formatting will work, though the later is easier.

Steps
------
1. Mark the whole column, or the range that you want to apply the format.
2. Select menu Format/Conditional formatting. A dialog will popup.
3. You will start will 'condition 1', select/enter the followings:
- Cell Value is
- between
- =TODAY()
- =TODAY()-7
4. Click the "Format" button. 'Format cell' dialog will pop up.
5. Chage 'color' to 'red'. Click 'OK'.

6. Click the 'Add' button to add another condition.
7. Repeat step 3-5; values to enter are
- Cell Value is
- between
- =TODAY()-8
- =TODAY()-14
8. Set color to 'yellow'

9. Click the 'Add' button to add another condition.
10.Repeat step 3-5; values to enter are
- Cell Value is
- between
- =TODAY()-15
- =TODAY()-21
11. Set color to 'green'

12.Click 'Ok' to close the 'Formatting' dialog.

That's all.


HTH -- arunkhemlai






Column A contains a list of date values (e.g. 11/04/2005).

I'm looking for a way to change the cell color based on the following
criteria ...

If the date falls earlier than today but no earlier than a week before
today then cell color to be red.

If the date falls earlier than today but no earlier than two weeks before
today then cell color to be yellow.

If the date falls earlier than today but no earlier than three weeks before
today then cell color to be green.

Can anyone offer a solution to this please? Should I be looking at
conditional formatting or should I use VBA?

This is a little limited for an extended project I have.
Can this process be expanded to include more than 3 criteria?
Regards
Roger


"arunkhemlai" wrote in message
...
Both VBA and conditional formatting will work, though the later is easier.

Steps
------
1. Mark the whole column, or the range that you want to apply the format.
2. Select menu Format/Conditional formatting. A dialog will popup.
3. You will start will 'condition 1', select/enter the followings:
- Cell Value is
- between
- =TODAY()
- =TODAY()-7
4. Click the "Format" button. 'Format cell' dialog will pop up.
5. Chage 'color' to 'red'. Click 'OK'.

6. Click the 'Add' button to add another condition.
7. Repeat step 3-5; values to enter are
- Cell Value is
- between
- =TODAY()-8
- =TODAY()-14
8. Set color to 'yellow'

9. Click the 'Add' button to add another condition.
10.Repeat step 3-5; values to enter are
- Cell Value is
- between
- =TODAY()-15
- =TODAY()-21
11. Set color to 'green'

12.Click 'Ok' to close the 'Formatting' dialog.

That's all.


HTH -- arunkhemlai






Column A contains a list of date values (e.g. 11/04/2005).

I'm looking for a way to change the cell color based on the following
criteria ...

If the date falls earlier than today but no earlier than a week before
today then cell color to be red.

If the date falls earlier than today but no earlier than two weeks before
today then cell color to be yellow.

If the date falls earlier than today but no earlier than three weeks
before
today then cell color to be green.

Can anyone offer a solution to this please? Should I be looking at
conditional formatting or should I use VBA?

Can this process be expanded to include more than 3 criteria?

Posted this earlier in the other branch ...

If you have more than 3 conditions to format, you'd probably need a macro

Here's the links to 2 previous posts by Dave Peterson & Bob Phillips
which illustrates how
a. http://tinyurl.com/3cex5 [by Dave Peterson]
b. http://tinyurl.com/2apja [by Bob Phillips]

Check out also:
a. JE McGimpsey's page at:
http://www.mcgimpsey.com/excel*/conditional6.html
which also contains links to Dave McRitchie's & Chip Pearson's CF pages

b. Bob Phillips' CFPlus - Extended Conditional Formatter
add-in at:
http://www.xldynamic.com/source/xld.....Download.html
(the write-up there states that up to 30 cond formats are catered for)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

A re-post of one of the links which should work properly now (tested again)
(think the previous posted link was hit by gremlins <g)

Check out also:
a. JE McGimpsey's page at:

http://www.mcgimpsey.com/excel/conditional6.html

which also contains links to Dave McRitchie's & Chip Pearson's CF pages

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----