Change cell color dependin on date ...

  • Thread starter Thread starter T. Denford
  • Start date Start date
T

T. Denford

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.
 
Yes. Try using conditional formatting on Column A.

Condition 1:
Cell Value Is - between - =TODAY() and =TODAY()-7

Condition 2:
Cell Value Is - between - =TODAY()-7 and =TODAY()-14

Condition 3:
Cell Value Is - between - =TODAY()-14 and =TODAY()-21


Adjust your formatting as appropriate for each color. You may also need to
test the various dates to assure that "-7", "-14", etc. is working for what
you need.

Hope this helps.
Paul
 
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
 
Try this ..

Select col A

Click Fornat > Conditional Formatting

Condition1:
Formula Is | =AND(ISNUMBER(A1),A1<TODAY(),A1>=TODAY()-7)
Click Format > Patterns tab > Red > OK

Condition 2:
Formula Is | =AND(ISNUMBER(A1),A1<TODAY(),A1>=TODAY()-14)
Click Format > Patterns tab > Yellow > OK

Condition 3:
Formula Is | =AND(ISNUMBER(A1),A1<TODAY(),A1>=TODAY()-21)
Click Format > Patterns tab > Green > OK

Click OK at the main dialog
 
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.
Click to expand...

Excellent. Thanks for the help everyone.

How could I do this if I had more than three conditions please?
 
T. Denford said:
How could I do this if I had more than three conditions please?
Click to expand...

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 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
 
You're welcome !
Thanks for feedback ..
Click to expand...

Max,

Sorry to bother you but I have pasted in the following code and am
struggling to know how to adapt it to suit my needs ...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

With Target
Select Case LCase(.Value)
Case Is = 1: .Interior.ColorIndex = 5
Case Is = 2: .Interior.ColorIndex = 6
Case Is = 3: .Interior.ColorIndex = 7
Case Is = 4: .Interior.ColorIndex = 8
Case Is = 5: .Interior.ColorIndex = 9
Case Is = 6: .Interior.ColorIndex = 10
Case Else
.Interior.ColorIndex = xlNone
End Select
End With

End Sub

I think I'm correct in saying that I need to build in an IF statement
somewhere within the CASE statement but not sure how to do this. I'm pretty
unfamiliar with all this VBA stuff and would appreciate some help.

Can you advise please?

Many thanks.

Tom Denford.
 
This is a little limited for an extended project I have.
Can this process be expanded to include more than 3 criteria?
Regards
Roger
 
Perhaps a re-cap of the subroutine implementation steps
mentioned by Dave P in his post: http://tinyurl.com/3cex5
would be useful here ..

".. Try this against a test worksheet.
Right click on the worksheet tab (say, Sheet1)
Select View Code
Paste this (the code below) in the code window
(usually the right hand side)
(Note: delete the defaults in the code window first)

Now get back to excel (press Alt+Q) and
type some numbers (1-6) in column A of that worksheet (i.e. Sheet1)."

With Dave's sub implemented in Sheet1 as per steps above, when you enter the
numbers 1 - 6 in col A, say in A1:A6, it will result in 6 different color
fills appearing

And if I'm not mistaken (Dave - pl correct me/add-on further here said:
Case Is = 1: .Interior.ColorIndex = 5
Case Is = 2: .Interior.ColorIndex = 6
Click to expand...
already carries the implicit IF conditions, viz. the above 2 would mean:
If number = 1, fill the cell with dark blue
If number = 2, fill the cell with yellow
and so on for all numbers 1 to 6 in the example sub

Have another go at getting Dave's example up !
If you need a working sample of the above, just drop me a line at either:
demechanik <at>yahoo<dot>com, or
xdemechanik <at>yahoo<dot>com
and I'll send it via private email

I would suggest however that you put in a fresh post in the
excel.programming ng if you need further help on how to tailor Dave's sub to
meet your specific requirements
 
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.CFPlus.Download.html
(the write-up there states that up to 30 cond formats are catered for)
 
Perhaps a re-cap of the subroutine implementation steps
mentioned by Dave P in his post: http://tinyurl.com/3cex5
would be useful here ..

".. Try this against a test worksheet.
Right click on the worksheet tab (say, Sheet1)
Select View Code
Paste this (the code below) in the code window
(usually the right hand side)
(Note: delete the defaults in the code window first)

Now get back to excel (press Alt+Q) and
type some numbers (1-6) in column A of that worksheet (i.e. Sheet1)."

With Dave's sub implemented in Sheet1 as per steps above, when you enter the
numbers 1 - 6 in col A, say in A1:A6, it will result in 6 different color
fills appearing


already carries the implicit IF conditions, viz. the above 2 would mean:
If number = 1, fill the cell with dark blue
If number = 2, fill the cell with yellow
and so on for all numbers 1 to 6 in the example sub

Have another go at getting Dave's example up !
If you need a working sample of the above, just drop me a line at either:
demechanik <at>yahoo<dot>com, or
xdemechanik <at>yahoo<dot>com
and I'll send it via private email

I would suggest however that you put in a fresh post in the
excel.programming ng if you need further help on how to tailor Dave's sub to
meet your specific requirements
Click to expand...

Thanks again Max. I have the example working but just need to tailor it to
suit the date ranges I need for my sheet.

Will have another crack and then re-post to programming newsgroup if I need
further assistance. Once again, many thanks for your help.
 
T. Denford said:
Thanks again Max. I have the example working but
just need to tailor it to suit the date ranges I need for my sheet.
Will have another crack and then re-post to programming
newsgroup if I need further assistance.
Click to expand...

You're welcome, Tom !
 
Back
Top