can i see the date the last time a cell was changed?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to figure out a formulla to make the date appear in one cell
everytime anouther cell's data is chaged.
 
There really isn't a worksheet formula to do that. Typically the date is
entered into a cell via = NOW() or another date function, but it will change
as time goes by. Now is Now, not Then. So, as Don Guilett has implied, it
is going to take some VBA code behind the scenes that detects when you make a
change in the cell(s) you are interested in tracking and then places the time
stamp of the (last?) change in a cell somewhere in a more permanent fashion.

If you need more help with this, just yell.
 
You need event code to do that.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
'for a single cell use this line
'If Target.Address = "$A$1" Then
'for a single column use this line
'If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the Sheet tab and "View Code". Copy/paste into that sheet
module.



Gord Dibben MS Excel MVP
 
Back
Top