Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Cells Based Upon Age of Info
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Cells Based Upon Age of Info
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 "Walter" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Cells Based Upon Age of Info
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Cells Based Upon Age of Info
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? "FSt1" wrote: 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 "Walter" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Cells Based Upon Age of Info
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 "Walter" wrote: 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? "FSt1" wrote: 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 "Walter" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling a few cells based on the info from another worksheet. | Excel Worksheet Functions | |||
Fill cells with color based on criteria in two cells | Excel Worksheet Functions | |||
Set color of cell based on info on another sheet? | Excel Worksheet Functions | |||
Run a command based on info in other cells | Excel Discussion (Misc queries) | |||
sum cells based upon color of cell | Excel Discussion (Misc queries) |