software

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 = quot;L2:L800quot;
If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
With Target
..Offset(0, -3).Value = Format(Date, quot;dd mmm yyyyquot;)
End With
End If

I'm assuming the code would look similar but I'm not that good... any help
would be appreciated.

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?

quot;Gary''s Studentquot; wrote:

gt; Couldn't test it, but try to add these three lines after setting the date:
gt;
gt; If .Offset(0, -3).Value lt; Now() Then
gt; .Offset(0, -3).Interior.ColorIndex = 32
gt; End If
gt; --
gt; Gary''s Student
gt;
gt;
gt; quot;Chrisquot; wrote:
gt;
gt; gt; To Anyone,
gt; gt;
gt; gt; I'm using a shared worksbook so I cannot use conditional formating BUT how
gt; gt; do I create a function that turns a cell a different color if the date is
gt; gt; older than todays date...
gt; gt; Currently, I'm using this code to add a date:
gt; gt; Const WS_RANGE1 As String = quot;L2:L800quot;
gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
gt; gt; With Target
gt; gt; .Offset(0, -3).Value = Format(Date, quot;dd mmm yyyyquot;)
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt; I'm assuming the code would look similar but I'm not that good... any help
gt; gt; would be appreciated.

Hi

Depending on the layout of your sheet, you could use Format/Conditional
Formatting.
Post back if you need some help with it.

Andy.

quot;Michaelquot; gt; wrote in message
...
gt; I'm looking for the exact same help. I'm not understanding the answer
gt; though. Here's what I have, we use a spreadsheet to track training dates.
gt; Training expries annually, semi-annual and quaterly. I have the cells
gt; formatted so that the next date training is required will be calculated
gt; automatically. However, there are alot of people, so i'm currently having
gt; to
gt; scan through the entire spreadsheet to locate expired dates. This would
gt; be
gt; much simpler if the expired dates automatically changed color upon the
gt; expiration date. Is this possible?
gt;
gt; quot;Gary''s Studentquot; wrote:
gt;
gt;gt; Couldn't test it, but try to add these three lines after setting the
gt;gt; date:
gt;gt;
gt;gt; If .Offset(0, -3).Value lt; Now() Then
gt;gt; .Offset(0, -3).Interior.ColorIndex = 32
gt;gt; End If
gt;gt; --
gt;gt; Gary''s Student
gt;gt;
gt;gt;
gt;gt; quot;Chrisquot; wrote:
gt;gt;
gt;gt; gt; To Anyone,
gt;gt; gt;
gt;gt; gt; I'm using a shared worksbook so I cannot use conditional formating BUT
gt;gt; gt; how
gt;gt; gt; do I create a function that turns a cell a different color if the date
gt;gt; gt; is
gt;gt; gt; older than todays date...
gt;gt; gt; Currently, I'm using this code to add a date:
gt;gt; gt; Const WS_RANGE1 As String = quot;L2:L800quot;
gt;gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
gt;gt; gt; With Target
gt;gt; gt; .Offset(0, -3).Value = Format(Date, quot;dd mmm yyyyquot;)
gt;gt; gt; End With
gt;gt; gt; End If
gt;gt; gt;
gt;gt; gt; I'm assuming the code would look similar but I'm not that good... any
gt;gt; gt; help
gt;gt; gt; would be appreciated.
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.

quot;Andyquot; wrote:

gt; Hi
gt;
gt; Depending on the layout of your sheet, you could use Format/Conditional
gt; Formatting.
gt; Post back if you need some help with it.
gt;
gt; Andy.
gt;
gt; quot;Michaelquot; gt; wrote in message
gt; ...
gt; gt; I'm looking for the exact same help. I'm not understanding the answer
gt; gt; though. Here's what I have, we use a spreadsheet to track training dates.
gt; gt; Training expries annually, semi-annual and quaterly. I have the cells
gt; gt; formatted so that the next date training is required will be calculated
gt; gt; automatically. However, there are alot of people, so i'm currently having
gt; gt; to
gt; gt; scan through the entire spreadsheet to locate expired dates. This would
gt; gt; be
gt; gt; much simpler if the expired dates automatically changed color upon the
gt; gt; expiration date. Is this possible?
gt; gt;
gt; gt; quot;Gary''s Studentquot; wrote:
gt; gt;
gt; gt;gt; Couldn't test it, but try to add these three lines after setting the
gt; gt;gt; date:
gt; gt;gt;
gt; gt;gt; If .Offset(0, -3).Value lt; Now() Then
gt; gt;gt; .Offset(0, -3).Interior.ColorIndex = 32
gt; gt;gt; End If
gt; gt;gt; --
gt; gt;gt; Gary''s Student
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Chrisquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; To Anyone,
gt; gt;gt; gt;
gt; gt;gt; gt; I'm using a shared worksbook so I cannot use conditional formating BUT
gt; gt;gt; gt; how
gt; gt;gt; gt; do I create a function that turns a cell a different color if the date
gt; gt;gt; gt; is
gt; gt;gt; gt; older than todays date...
gt; gt;gt; gt; Currently, I'm using this code to add a date:
gt; gt;gt; gt; Const WS_RANGE1 As String = quot;L2:L800quot;
gt; gt;gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
gt; gt;gt; gt; With Target
gt; gt;gt; gt; .Offset(0, -3).Value = Format(Date, quot;dd mmm yyyyquot;)
gt; gt;gt; gt; End With
gt; gt;gt; gt; End If
gt; gt;gt; gt;
gt; gt;gt; gt; I'm assuming the code would look similar but I'm not that good... any
gt; gt;gt; gt; help
gt; gt;gt; gt; would be appreciated.
gt;
gt;
gt;

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 =D1gt;TODAY()
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.

quot;Michaelquot; gt; wrote in message
...
gt; Thank you for the help Andy. I applied your suggestion. However, I'm
gt; sorry
gt; to report it's not working. Seems like the perfect solution, maybe I'm
gt; just
gt; applying it incorrectly.
gt;
gt; I have a spreadsheet which contains training requirements for all
gt; personnel.
gt; I have two columns for each training requirement. 1st column lists when
gt; the
gt; training was conducted. 2nd column lists when training is next due (this
gt; column contains a formula which calculates the date from which training
gt; was
gt; conducted).
gt;
gt; I've applied your suggestion, which, logically, makes sense. However, it
gt; doesn't work for some reason. I've tried all possiblities in the second
gt; column (between, equal too, etc...). It's possible that I'm entering the
gt; last bit of information in wrong.
gt;
gt; quot;Andyquot; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; Depending on the layout of your sheet, you could use Format/Conditional
gt;gt; Formatting.
gt;gt; Post back if you need some help with it.
gt;gt;
gt;gt; Andy.
gt;gt;
gt;gt; quot;Michaelquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; I'm looking for the exact same help. I'm not understanding the answer
gt;gt; gt; though. Here's what I have, we use a spreadsheet to track training
gt;gt; gt; dates.
gt;gt; gt; Training expries annually, semi-annual and quaterly. I have the cells
gt;gt; gt; formatted so that the next date training is required will be calculated
gt;gt; gt; automatically. However, there are alot of people, so i'm currently
gt;gt; gt; having
gt;gt; gt; to
gt;gt; gt; scan through the entire spreadsheet to locate expired dates. This
gt;gt; gt; would
gt;gt; gt; be
gt;gt; gt; much simpler if the expired dates automatically changed color upon the
gt;gt; gt; expiration date. Is this possible?
gt;gt; gt;
gt;gt; gt; quot;Gary''s Studentquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Couldn't test it, but try to add these three lines after setting the
gt;gt; gt;gt; date:
gt;gt; gt;gt;
gt;gt; gt;gt; If .Offset(0, -3).Value lt; Now() Then
gt;gt; gt;gt; .Offset(0, -3).Interior.ColorIndex = 32
gt;gt; gt;gt; End If
gt;gt; gt;gt; --
gt;gt; gt;gt; Gary''s Student
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Chrisquot; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; To Anyone,
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I'm using a shared worksbook so I cannot use conditional formating
gt;gt; gt;gt; gt; BUT
gt;gt; gt;gt; gt; how
gt;gt; gt;gt; gt; do I create a function that turns a cell a different color if the
gt;gt; gt;gt; gt; date
gt;gt; gt;gt; gt; is
gt;gt; gt;gt; gt; older than todays date...
gt;gt; gt;gt; gt; Currently, I'm using this code to add a date:
gt;gt; gt;gt; gt; Const WS_RANGE1 As String = quot;L2:L800quot;
gt;gt; gt;gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
gt;gt; gt;gt; gt; With Target
gt;gt; gt;gt; gt; .Offset(0, -3).Value = Format(Date, quot;dd mmm yyyyquot;)
gt;gt; gt;gt; gt; End With
gt;gt; gt;gt; gt; End If
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I'm assuming the code would look similar but I'm not that good...
gt;gt; gt;gt; gt; any
gt;gt; gt;gt; gt; help
gt;gt; gt;gt; gt; would be appreciated.
gt;gt;
gt;gt;
gt;gt;