Tracking Change Date

[Guest]

I want to track the date last changed of several cells, so anyone looking at
the information can easily see the last updated date. I want the date last
changed to show in an adjucant cell. I don't just want to use track changes.

 

[JE McGimpsey]

Take a look here:

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

 

[Guest]

Assuming the change is initiated manually, you can use a macro to do this.

Right click on the worksheet tab and select view code. in the resulting
module, in the dropdowns at the top of the module, in the left dropdown
select worksheet and in the right dropdown select Change (not Selection
Change). You will get an event declaration like:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

this macro will fire when a cell is edited (whether the value is actually
changed or not). You can have it update an adjacent cell with the date and
time

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error goto ErrHandler
set rng = Intersect(Target,columns(3))
if not rng is nothing then
Application.EnableEvents = False
for each cell in rng
' if not isempty(cell) then
cell.offset(0,1).Value = Now
cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
cell.EntireColumn.Autofit
' end if
Next
ErrHandler:
Application.EnableEvents = True
End Sub

As written it reacts to changes in column C. Adjust to suit your needs.

If you want to don't want to record a date if the cell is cleared, then
remove the single quotes in two places.

 

[Ardus Petus]

Paste the following code into your worksheet's code page
(Right click on worksheet tab and select Code)

HTH
--
AP

'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Puts date stamp in next column
' if changed cell belongs to range specified py strMyCells

' Change string value according to your needs
Const strMyCells = "A1,B2:B10,C:C"
Dim ocell As Range
Application.EnableEvents = False
For Each ocell In Intersect(Target, Range(strMyCells))
ocell.Offset(0, 1).Value = Date
Next ocell
Application.EnableEvents = True
End Sub
'----------------------------------

 

[Guest]

Tom,

Thanks for the information. I used your macro but I receive the below error
when I click in a cell in column "C" to make a change:

Compile error:
Block If without End If

Also, I think your code below had one type-o:
cell.offset(0,1).NumberFormat:="mm/dd/yyyy hh:mm"
I dont beleive there should be a collon before the equal sign. Maybe this is
my problem but I copied and pasted the macro I used for your reference. It is
exactly the same as yours with the one change I mentioned above.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandler
Set rng = Intersect(Target, Columns(3))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
'if not isempty (cell) then
cell.Offset(0, 1).Value = Now
cell.Offset(0, 1).NumberFormat = "mm/dd/yyy hh:mm"
cell.EntireColumn.AutoFit
'end if
Next
ErrHandler:
Application.EnableEvents = True
End Sub

Please let me know if you can see any reason why I would be receiving the
error.

Thanks for your help!

Nicole

 

[Guest]

1. I said don't use selectionchange and then copied it in without noticing.
Anyway, this is tested and works:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Set rng = Intersect(Target, Columns(3))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
' if not isempty(cell) then
cell.Offset(0, 1).Value = Now
cell.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm"
cell.Offset(0, 1).EntireColumn.AutoFit
' end if
Next
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Guest]

Thank you very much Tom. This works beautifully! Hope you have a great day!