Color Cells Based Upon Age of Info

[Guest]

I have a column of dates and want to color cells based upon the age of the
information in that row. Date criteria is 1 week, 2-4 weeks, 2 months, 3
months, 6 months, 1 year, and > 1 year. Each of the criteria is a different
color. I don't care if the whole row turns the particular color instead of
one cell in that row. The source of the date is pulled every Monday so all
the dates will be on a Monday. Plus the colors do not matter as long as they
as distinct from one another. Here is a sample:

Joe Smith 6/4/2007 Supplier
Judy Smith 5/21/2007 Supplier
John Doe 4/11/2007 Supplier
Jane Doe 3/11/2007 Supplier
Ralph Doe 12/11/2006 Supplier
Don Smith 6/12/2006 Supplier
Nero Smith 3/20/2006 Supplier

If this is too much work I can always just manually fill in the colors but
since I am doing this report every week it would be nice to automate some of
it so it is not so tedious. Please let me know if you need additional info.

 

[Guest]

hi,
2 questions.
1. what column is the date in?
2. what is the data RANGE of the date criteria? otherwize you may end up
with big uncolored holes in your data.

Regards
FSt1

 

[Dave O]

Conditional Formatting doesn't provide enough options to cover 6
alternatives. Instead you can copy and paste the following code as a
macro. To run it, highlight the dates and run the macro. The lines
that read "rCell.Interior.Colorindex = #" assign background colors to
the cell; the number is the color number. I chose numbers that were
easy to type, but you can choose different colors by using numbers
from this list:

red 3
pink 7
rose 38
orange 46
light orange 45
gold 44
tan 40
lime 43
light yellow 36
yellow 6
bright green 4
light green 35
aqua 42
turquoise 8
light turquoise 34
sky blue 33
pale blue 34
lavender 39
25% grey 15


Sub Color_The_Date()
Dim rCell As Range

For Each rCell In Selection.Cells
If Int(Now()) - rCell.Value <= 7 Then rCell.Interior.ColorIndex = 3
If Int(Now()) - rCell.Value > 7 And Int(Now()) - rCell.Value <= 14
Then rCell.Interior.ColorIndex = 7
If Int(Now()) - rCell.Value > 14 And Int(Now()) - rCell.Value <= 60
Then rCell.Interior.ColorIndex = 6
If Int(Now()) - rCell.Value > 60 And Int(Now()) - rCell.Value <= 180
Then rCell.Interior.ColorIndex = 4
If Int(Now()) - rCell.Value > 180 And Int(Now()) - rCell.Value <=
365 Then rCell.Interior.ColorIndex = 8
If Int(Now()) - rCell.Value >= 365 Then rCell.Interior.ColorIndex =
15
Next rCell

End Sub

 

[Guest]

ColA
Data range will vary every week but for this week it goes from 8/9/2005 to
5/7/2007. Or the data goes from a2 to a75 but this will vary each week as
well. Is that what you mean?

 

[Guest]

hi Walter,
for the column, yes. but for the date ranges, no.
you said your first date criteria was 1 week. does that mean from today out
1 week? your next criteria was 2-4 weeks. does that mean > today + 7 and <
today + 30? You listed 7 criteria's.
See Dave O's post. his may work for you. I was planning something similar.

Regards
FSt1