3

I need a way to timestamp an adjacent cell whose value changes via formula. Using this as an example I need the cell adjacent to A1 on sheet 1 to timestamp the date and time when the cell value changed.

The example I have linked to above initiates a message box when the cell value changes via formula (worksheet_change events don't appear to recognise changes to a cell value when it contains a formula whose value is changed because of a cell change elsewhere). I don't want the message box but I do want a timestamp.

For simplicity I will post the instructions at that linked question here, any additional help with this specific question is appreciated.

In Sheet1 Cell A1, put this formula 

=Sheet2!A1+1

Now In a module paste this code

Public PrevVal As Variant

Paste this in the Sheet Code area

Private Sub Worksheet_Calculate()
    If Range("A1").Value <> PrevVal Then
        MsgBox "Value Changed"
        PrevVal = Range("A1").Value
    End If
End Sub

And lastly in the ThisWorkbook Code area paste this code

Private Sub Workbook_Open()
    PrevVal = Sheet1.Range("A1").Value
End Sub
Community
  • 1
  • 1
Andy
  • 1,422
  • 5
  • 27
  • 43

1 Answers1

2

Change MsgBox "Value Changed" to:

Range("B1").Value = Format(Now, "dd/mm/yyyy hh:mm:ss")

or whichever timestamp format you require

CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • 1
    Wow, thanks for the fast reply. That worked perfectly. – Andy Apr 03 '17 at 12:40
  • Can this be modified to accommodate a range of cells, say A1:A10? – Andy Apr 03 '17 at 13:24
  • 1
    @Andy You would need to keep your previous values in an array - it's not very difficult to change the code you currently have, but it's certainly different enough that you should ask a new question. :) – CallumDA Apr 03 '17 at 13:30
  • I've learnt that starting a new question closely related to an existing one is like walking through land mines but I'll take that chance ;) – Andy Apr 03 '17 at 13:32
  • http://stackoverflow.com/questions/43186207/timestamp-when-a-cell-in-a-larger-cell-range-is-changed-via-a-formula-excel – Andy Apr 03 '17 at 13:41