I'm creating a prioritized list using Excel 2003 that will be updated
periodically. I would like to know if there is a way to capture the last date
when a priority was changed. For example, if A1 contains a priority of level
1, then B1 would contain the date of when I entered that priority. Now, let's
say after a few weeks that priority is changed to level 2, I now want B2 to
show the new date of when the change to 2 was made. Any ideas on how to do
this?

Clarification please... Is A1 the only cell that is being monitored? If so,
are you listing the dates a changed was made in a running list down Column
B? I ask because your post said B1 for the first change and B2 for the
second change, but only ever mentioned A1 as the priority cell.

--
Rick (MVP - Excel)


"robrowe" wrote in message
...
I'm creating a prioritized list using Excel 2003 that will be updated
periodically. I would like to know if there is a way to capture the last
date
when a priority was changed. For example, if A1 contains a priority of
level
1, then B1 would contain the date of when I entered that priority. Now,
let's
say after a few weeks that priority is changed to level 2, I now want B2
to
show the new date of when the change to 2 was made. Any ideas on how to do
this?

Hi,

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:A100"))
If Not isect Is Nothing Then
Target.Offset(0,1) = Date
End If
End Sub
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"robrowe" wrote:

I'm creating a prioritized list using Excel 2003 that will be updated
periodically. I would like to know if there is a way to capture the last date
when a priority was changed. For example, if A1 contains a priority of level
1, then B1 would contain the date of when I entered that priority. Now, let's
say after a few weeks that priority is changed to level 2, I now want B2 to
show the new date of when the change to 2 was made. Any ideas on how to do
this?

Shane

I would click on the "maybe" button depending upon answers to Rick's query.

Your code will overwrite B1 when a new priority number is entered in A1

From OP's description, sounds like B1 date should remain as was and B2 to
get new date.


Gord


On Mon, 9 Feb 2009 08:41:02 -0800, Shane Devenshire
wrote:

Hi,

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:A100"))
If Not isect Is Nothing Then
Target.Offset(0,1) = Date
End If
End Sub