CODEWITHSUNDEEP
excelvba
Sara Va Nan
Sara Va Nan

Reputation: 1

How to update a cell in Excel VBA with NOW time for a another cell update which in turn was updated via other cell changes?

I need to update Cell E2 with Now(), for any value change in cell D2.

Note D2 value depends on many other cells like A2, B2 and C2.

I tried Worksheet_Change, but this triggers for manual update, also the cell which I need to update E2 is updated automatically (i.e. not via manual update).

Any solution, where cells A2, B2 and C2 are getting live feeds and `D2 has a formula based on them, is acceptable.

Now if D2 changes, I need E2 with Now() date and time.

Upvotes: 0

Views: 1490

Answers (2)

Sara Va Nan
Sara Va Nan

Reputation: 1

I solved the problem by using the Precedent property of the Range object.

I checked if the changed cell is part of my precedent, if so then using the Row of the Target (changed cell), i moved to my required Column (which is static) did my logic.

Thanks, Sara

Private Sub Worksheet_Change(ByVal Target As Range) Dim keycells As Range Set keycells = Sheets("FUT_CALLS-1").Range("AD1:AD1").EntireColumn

If Not Intersect(Target, keycells.Precedents) Is Nothing Then

If (Sheets("FUT_CALLS-1").Cells(Target.Row, 30).Value = "BUY" Or Sheets("FUT_CALLS-1").Cells(Target.Row, 30).Value = "SELL") And Sheets("FUT_CALLS-1").Cells(Target.Row, 30).Value <> Sheets("MyCodeSheet").Cells(Target.Row, 1).Value Then
   Sheets("FUT_CALLS-1").Cells(Target.Row, 32).Value = Now()
   Sheets("MyCodeSheet").Cells(Target.Row, 1).Value = Sheets("FUT_CALLS-1").Cells(Target.Row, 30).Value

End If

End If End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You can use the Calculate event with a memory variable:

Public OldD2 As Variant

Private Sub Worksheet_Calculate()
   If Range("D2").Value <> OldD2 Then
      Application.EnableEvents = False
         Range("E2").Value = Now
         OldD2 = Range("D2").Value
      Application.EnableEvents = True
   End If
End Sub

Upvotes: 1

Related Questions