record a date a cell was changed

[Ian]

I need to record the date a particular cell is filled or changed. I was
thinking I could create a formula that would enter the current date whenever
anything is entered into a cell. For instance when cell A1 has something
entered or changed in it A2 gets the date the cell was changed placed in it.
Any good ideas?

 

[Paul B]

Ian, here is one way,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 1 And .Row = 1 Then
'change Now to Date if you don't want the time
.Offset(0, 1).Value = Now()

End If
End With
End Sub

To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Carim]

Hi,

all the solutions to your problem :
http://www.mcgimpsey.com/excel/timestamp.html

HTH
Cheers
Cari

 

[Adept]

Have you considered using the Comment Function.

I write the date to a Comment Box on each change. For some, I
add a date where I want a running total. For others, I just over
write the existing data.

I then check the date before updating the cell to ensure the new
data is truly 'newer' than the existing data.

The Old Grey Geek
--

The Old Grey Geeks' Lament

And we carried our bits in a bucket,
And our mainframe weighed 900 tons,
And we programmed in ones and in zeros
And sometimes we ran out of ones.

 

[Ian]

sorry Paul, I don't know a lot of VBA. What if I want to specificy a range
of cells. For instance my target cells (the ones I want to monitor for a
change) are A1:C3 and I want the corresponding date of changes to be placed
into cells D1:F3

 

[JE McGimpsey]

Modifying slightly from

http://www.mcgimpsey.com/excel/datestamp.html

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1:C3"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 3).ClearContents
Else
With .Offset(0, 3)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[Kokanutt]

Mr. McGimpsey, your code is very useful. Thank you for helping me get
started. However, I need the code to put a date stamp in column 36(AJ) of my
target row i.e. AJ2, AJ3, AJ544, etc. Your code when using the "offset"
function puts the date stamp 36 columns to right of the target cell or cell
changed. Can you revise your code for my need? My attempts have been
unsuccessful.

 

[Dave Peterson]

Instead of using:
..Offset(0, 3)
try:
me.cells(target.row,"AJ")