1

The following code is what gives me an initial timestamp (N) and updated time (O).

This works when the cell's in column D on WORKSHEET1 are manually updated.

The cell's in Column D WORKSHEET1 reference WORKSHEET2 Column E now. So for example D4 on WORKSHEET1 is ='WORKSHEET2'!E23.

When values are updated on WORKSHEET2, the D column on WORKSHEET1 updates automatically.

The timestamp code below then stops working and does not pick up this value change. Can I insert a VBA code so that when i change the values on WORKSHEET2, and it updates on WORKSHEET1 because of the formula in cel D, the timestamp will work?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range

Dim myDateTimeRage As Range

Dim myUpdatedRange As Range

Set myTableRange = Range("D1:D314")

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

Set myDateTimeRage = Range("N" & Target.Row)

Set myUpdatedRange = Range("O" & Target.Row)

If myDateTimeRage.Value = "" Then

    myDateTimeRage.Value = Now
End If

myUpdatedRange.Value = Now

End Sub
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
leslie
  • 11
  • 1
  • This code runs when a change is detected in the worksheet the code is saved to. Also, without qualify a sheet, `Range()` will refer to the active sheet (which I assume is the same one that stores this code). – BruceWayne Nov 26 '19 at 20:04
  • 1
    Put the code in Worksheet2 code and then change the cells on worksheet1. – Scott Craner Nov 26 '19 at 20:13
  • So should i use with after i set my range? to reference the range on the non-active sheet? not sure what function to use – leslie Nov 27 '19 at 16:02

1 Answers1

0

Try this - 

Private Sub Worksheet_Calculate()
    Dim myTableRange As Range
    Dim myDateTimeRage As Range
    Dim myUpdatedRange As Range
    Set myTableRange = Range("D1:D314")   

    For Each cell In myTableRange
        Dim OldValue As Variant
        Application.EnableEvents = False
        Application.Undo
        OldValue = cell.Value
        Application.Undo
        Application.EnableEvents = True
        If OldValue <> cell.Value Then
            Set myDateTimeRage = Range("N" & cell.Row)
            Set myUpdatedRange = Range("O" & cell.Row)    
                If myDateTimeRage.Value = "" Then
                    myDateTimeRage.Value = Now
                End If
            myUpdatedRange.Value = Now
        End If    
    Next cell
Miles Fett
  • 711
  • 4
  • 17
Nakul Sanyashi
  • 1