Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Color Change
To Anyone,
I'm using a shared worksbook so I cannot use conditional formating BUT how do I create a function that turns a cell a different color if the date is older than todays date... Currently, I'm using this code to add a date: Const WS_RANGE1 As String = "L2:L800" If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then With Target ..Offset(0, -3).Value = Format(Date, "dd mmm yyyy") End With End If I'm assuming the code would look similar but I'm not that good... any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Color Change
I'm looking for the exact same help. I'm not understanding the answer
though. Here's what I have, we use a spreadsheet to track training dates. Training expries annually, semi-annual and quaterly. I have the cells formatted so that the next date training is required will be calculated automatically. However, there are alot of people, so i'm currently having to scan through the entire spreadsheet to locate expired dates. This would be much simpler if the expired dates automatically changed color upon the expiration date. Is this possible? "Gary''s Student" wrote: Couldn't test it, but try to add these three lines after setting the date: If .Offset(0, -3).Value < Now() Then .Offset(0, -3).Interior.ColorIndex = 32 End If -- Gary''s Student "Chris" wrote: To Anyone, I'm using a shared worksbook so I cannot use conditional formating BUT how do I create a function that turns a cell a different color if the date is older than todays date... Currently, I'm using this code to add a date: Const WS_RANGE1 As String = "L2:L800" If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then With Target .Offset(0, -3).Value = Format(Date, "dd mmm yyyy") End With End If I'm assuming the code would look similar but I'm not that good... any help would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Color Change
Hi
Depending on the layout of your sheet, you could use Format/Conditional Formatting. Post back if you need some help with it. Andy. "Michael" wrote in message ... I'm looking for the exact same help. I'm not understanding the answer though. Here's what I have, we use a spreadsheet to track training dates. Training expries annually, semi-annual and quaterly. I have the cells formatted so that the next date training is required will be calculated automatically. However, there are alot of people, so i'm currently having to scan through the entire spreadsheet to locate expired dates. This would be much simpler if the expired dates automatically changed color upon the expiration date. Is this possible? "Gary''s Student" wrote: Couldn't test it, but try to add these three lines after setting the date: If .Offset(0, -3).Value < Now() Then .Offset(0, -3).Interior.ColorIndex = 32 End If -- Gary''s Student "Chris" wrote: To Anyone, I'm using a shared worksbook so I cannot use conditional formating BUT how do I create a function that turns a cell a different color if the date is older than todays date... Currently, I'm using this code to add a date: Const WS_RANGE1 As String = "L2:L800" If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then With Target .Offset(0, -3).Value = Format(Date, "dd mmm yyyy") End With End If I'm assuming the code would look similar but I'm not that good... any help would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Color Change
Thank you for the help Andy. I applied your suggestion. However, I'm sorry
to report it's not working. Seems like the perfect solution, maybe I'm just applying it incorrectly. I have a spreadsheet which contains training requirements for all personnel. I have two columns for each training requirement. 1st column lists when the training was conducted. 2nd column lists when training is next due (this column contains a formula which calculates the date from which training was conducted). I've applied your suggestion, which, logically, makes sense. However, it doesn't work for some reason. I've tried all possiblities in the second column (between, equal too, etc...). It's possible that I'm entering the last bit of information in wrong. "Andy" wrote: Hi Depending on the layout of your sheet, you could use Format/Conditional Formatting. Post back if you need some help with it. Andy. "Michael" wrote in message ... I'm looking for the exact same help. I'm not understanding the answer though. Here's what I have, we use a spreadsheet to track training dates. Training expries annually, semi-annual and quaterly. I have the cells formatted so that the next date training is required will be calculated automatically. However, there are alot of people, so i'm currently having to scan through the entire spreadsheet to locate expired dates. This would be much simpler if the expired dates automatically changed color upon the expiration date. Is this possible? "Gary''s Student" wrote: Couldn't test it, but try to add these three lines after setting the date: If .Offset(0, -3).Value < Now() Then .Offset(0, -3).Interior.ColorIndex = 32 End If -- Gary''s Student "Chris" wrote: To Anyone, I'm using a shared worksbook so I cannot use conditional formating BUT how do I create a function that turns a cell a different color if the date is older than todays date... Currently, I'm using this code to add a date: Const WS_RANGE1 As String = "L2:L800" If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then With Target .Offset(0, -3).Value = Format(Date, "dd mmm yyyy") End With End If I'm assuming the code would look similar but I'm not that good... any help would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Color Change
Hi
What is the formula you are using in conditional formatting? I reckon you'll need something like this, with your dates in column D: Formula Is along with =D1TODAY() Select the whole of column D and then enter the above into the CF boxes. This will automatically spawn the formula to the whole column for you. Andy. "Michael" wrote in message ... Thank you for the help Andy. I applied your suggestion. However, I'm sorry to report it's not working. Seems like the perfect solution, maybe I'm just applying it incorrectly. I have a spreadsheet which contains training requirements for all personnel. I have two columns for each training requirement. 1st column lists when the training was conducted. 2nd column lists when training is next due (this column contains a formula which calculates the date from which training was conducted). I've applied your suggestion, which, logically, makes sense. However, it doesn't work for some reason. I've tried all possiblities in the second column (between, equal too, etc...). It's possible that I'm entering the last bit of information in wrong. "Andy" wrote: Hi Depending on the layout of your sheet, you could use Format/Conditional Formatting. Post back if you need some help with it. Andy. "Michael" wrote in message ... I'm looking for the exact same help. I'm not understanding the answer though. Here's what I have, we use a spreadsheet to track training dates. Training expries annually, semi-annual and quaterly. I have the cells formatted so that the next date training is required will be calculated automatically. However, there are alot of people, so i'm currently having to scan through the entire spreadsheet to locate expired dates. This would be much simpler if the expired dates automatically changed color upon the expiration date. Is this possible? "Gary''s Student" wrote: Couldn't test it, but try to add these three lines after setting the date: If .Offset(0, -3).Value < Now() Then .Offset(0, -3).Interior.ColorIndex = 32 End If -- Gary''s Student "Chris" wrote: To Anyone, I'm using a shared worksbook so I cannot use conditional formating BUT how do I create a function that turns a cell a different color if the date is older than todays date... Currently, I'm using this code to add a date: Const WS_RANGE1 As String = "L2:L800" If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then With Target .Offset(0, -3).Value = Format(Date, "dd mmm yyyy") End With End If I'm assuming the code would look similar but I'm not that good... any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
options to update automatic links | Excel Worksheet Functions | |||
how can i add automatic bottom border in a continious table | Excel Discussion (Misc queries) | |||
How can I find automatic links in an Excel-sheet? | Excel Discussion (Misc queries) | |||
find automatic page breaks | Excel Worksheet Functions | |||
Automatic backup copy | Charts and Charting in Excel |