tracking changes to cells

  • Thread starter Thread starter Ms_M_o_n_i_c_a
  • Start date Start date
M

Ms_M_o_n_i_c_a

how do i do this?

example: when a1 is populated with any value, the time & date of that entry
was made automatically populates a2 and b2 shows the entry. then tomorrow
when a1 is updated, the time & date/update populates into a3/b3. a2&a3
remains the same.

i tried TRACK CHANGES but the history tab kept deleting on every save AND it
wouldnt track changes made on the second sheet of the workbook. maybe i was
doing something wrong.
 
Hi,

Right click your sheet tab, view code and paste this in. Note it works on a1
- a10 which you can change but if you really only want it to work on a1 use
the commented out line

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
'If Target.Address = "$A$1" Then
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Range("IV" & Target.Row).End(xlToLeft).Offset(, 1).Value = Date
Range("IV" & Target.Row).End(xlToLeft).Offset(, 1).Value = Time
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub

Mike
 
Monica
This little macro will do that. Place it in the sheet module of your
sheet. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
Application.EnableEvents = False
Dest.Value = Now
Dest.Offset(, 1).Value = Range("A1").Value
Range("A1").ClearContents
Application.EnableEvents = True
End If
End Sub
 
Back
Top