Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Change cell color dependin on date ...
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? |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Change cell color dependin on date ...
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? |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Change cell color dependin on date ...
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 ---- |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Change cell color dependin on date ...
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Due date - change cell color | Excel Worksheet Functions | |||
how can I get a cell to change color by date to indicate expiery t | New Users to Excel | |||
change background row color with change of date in a cell | Excel Discussion (Misc queries) | |||
cell color change based on due date | New Users to Excel | |||
Change cell color dependin on date ... | Excel Worksheet Functions |