Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Colors Dependant on Date
If I were to assign "=NOW()" to say...cell A10 and have column C dedicated to delivery dates for customers, could I have the cells automatically change colors the closer it gets the date in cell A10? For instance: If any of the delivery dates were 2 weeks away then the cells would turn green, 1 week away those cells would turn limegreen, 3 days away they would turn yellow and past due would be red. Thnx for any help -- RJM ------------------------------------------------------------------------ RJM's Profile: http://www.excelforum.com/member.php...o&userid=24822 View this thread: http://www.excelforum.com/showthread...hreadid=551179 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Colors Dependant on Date
See Conditional Formatting under the Format menu.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "RJM" wrote in message ... If I were to assign "=NOW()" to say...cell A10 and have column C dedicated to delivery dates for customers, could I have the cells automatically change colors the closer it gets the date in cell A10? For instance: If any of the delivery dates were 2 weeks away then the cells would turn green, 1 week away those cells would turn limegreen, 3 days away they would turn yellow and past due would be red. Thnx for any help -- RJM ------------------------------------------------------------------------ RJM's Profile: http://www.excelforum.com/member.php...o&userid=24822 View this thread: http://www.excelforum.com/showthread...hreadid=551179 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Colors Dependant on Date
you can do 3 colors with Data=Validation.
-- Regards, Tom Ogilvy "RJM" wrote: If I were to assign "=NOW()" to say...cell A10 and have column C dedicated to delivery dates for customers, could I have the cells automatically change colors the closer it gets the date in cell A10? For instance: If any of the delivery dates were 2 weeks away then the cells would turn green, 1 week away those cells would turn limegreen, 3 days away they would turn yellow and past due would be red. Thnx for any help -- RJM ------------------------------------------------------------------------ RJM's Profile: http://www.excelforum.com/member.php...o&userid=24822 View this thread: http://www.excelforum.com/showthread...hreadid=551179 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Colors Dependant on Date
Debra Dalgleish's site has extensive information on conditional formatting as
well: http://www.contextures.com/tiptech.html -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: you can do 3 colors with Data=Validation. -- Regards, Tom Ogilvy "RJM" wrote: If I were to assign "=NOW()" to say...cell A10 and have column C dedicated to delivery dates for customers, could I have the cells automatically change colors the closer it gets the date in cell A10? For instance: If any of the delivery dates were 2 weeks away then the cells would turn green, 1 week away those cells would turn limegreen, 3 days away they would turn yellow and past due would be red. Thnx for any help -- RJM ------------------------------------------------------------------------ RJM's Profile: http://www.excelforum.com/member.php...o&userid=24822 View this thread: http://www.excelforum.com/showthread...hreadid=551179 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Colors Dependant on Date
You can easily do this with Conditional Formatting using simple date
subtraction. Do you have to do it with VBA? You don't have to store today's date in another cell. Format/Cond Formatting Condition 1(2 weeks away): -Change from Cell Value Is to Formula Is =and(TODAY()-C17,NOW()-C1<=14) -choose formatting for this condition Condition 2(1 week away): -Change from Cell Value Is to Formula Is =TODAY()-C1<=7 -choose formatting for this condition Good luck RJM wrote: If I were to assign "=NOW()" to say...cell A10 and have column C dedicated to delivery dates for customers, could I have the cells automatically change colors the closer it gets the date in cell A10? For instance: If any of the delivery dates were 2 weeks away then the cells would turn green, 1 week away those cells would turn limegreen, 3 days away they would turn yellow and past due would be red. Thnx for any help -- RJM ------------------------------------------------------------------------ RJM's Profile: http://www.excelforum.com/member.php...o&userid=24822 View this thread: http://www.excelforum.com/showthread...hreadid=551179 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Colors Dependant on Date
Thnx guys, will give it a sho -- RJ ----------------------------------------------------------------------- RJM's Profile: http://www.excelforum.com/member.php...fo&userid=2482 View this thread: http://www.excelforum.com/showthread.php?threadid=55117 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
Automatically change cell colors based on date in cell | Excel Discussion (Misc queries) | |||
Can a cell change colors when the date entered arrives? | Excel Discussion (Misc queries) | |||
Cell colors or text color changing when date in cell gets closer. | Excel Worksheet Functions | |||
Date dependant values. | Excel Discussion (Misc queries) |