Running time stamp column each time a specific cell changes.

[Hootywho]

Hello, I have searched and not come up with anything I can modify for my purpose. I have a specific cell "B321" that has a formula and changes regularly throughout the day based on entries elsewhere within the workbook. This cell value can change as many as 250 times from open to close. My goal is to have a seperate worksheet (hidden) named "timestamp info" collect in column "A" the value of B321 when it initially changes from "" and column "B" would show actual time this event occured. Columns A & B would continue to collect this info each time B321 changes.

Many Thanks

 

[RokTikiTiki]

I know this is an older thread, but I found it and it is going to work perfectly for me. However, I am running into an issue. If I run this code on any page but Sheet1 (even when I put it on Sheet4 with the code in red stating Sheet4), it will not update properly. It works on Sheet1, but no other Sheet:
Code:


Option Explicit

Private Sub Worksheet_Calculate()

Dim iLastRow As Long
Dim tsi As Worksheet
Dim mws As Worksheet

Set tsi = ThisWorkbook.Sheets("Time Stamp")
tsi.Range("A2") = "Event"
tsi.Range("B2") = "Date/Time Changed"
iLastRow = tsi.Cells(tsi.Rows.Count, 1).End(xlUp).Row

Set mws = ThisWorkbook.Sheets("Sheet4")

If mws.Range("E3").Value <> tsi.Cells(iLastRow, 1).Value Then
tsi.Cells(iLastRow + 1, 1) = mws.Range("E3").Value
tsi.Cells(iLastRow + 1, 2) = Format(Now(), "dd/mm/yyyy hh:nn:ss")
End If

End Sub



Thanks in advance for any insight you can provide.