Date doesn’t auto fill when the cell updates data automatically but manually when I change data it works well

Question

Private Sub Worksheet Change (ByVal Target As Range)
Dim MyDataRng Às Range
Set MyDataRng = Range ("A2:A10")
If Intersect (Target, MyDataRng) Is Tawning Then Exit sub
On Error Resume Next
If Target.Offset (0, 1)
• Then
Target.Offset (0, 1) = Now
End If
Target.Offset (0, 2) = Now

I was trying to get auto filled date and time to my worksheet. When I enter anything within the added columns it works fine. But when the sheet auto fills from the other linked sheets it doesn’t update. I manually have to enter it. WHY?

Is your code _exactly_ as you posted here, or have you modified it for posting here? — chris neilsen, Nov 28 '22 at 05:34
Changes in cell values due to formulas do not trigger the `Worksheet_Change` event. You have to use the Calculate event instead. — Tim Williams, Nov 28 '22 at 06:42
https://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula — Siddharth Rout, Nov 28 '22 at 07:11
@VBasic2008 I actually couldn't get done what i exactly wanted so I used this formula instead =IF(C3<>"",IF(D3="",NOW(),D3),"") but it doesn't work the way I want it. — Eranga Kasun, Dec 02 '22 at 04:43
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyDataRng As Range Set MyDataRng = Range("G2:G58") If Intersect(Target, MyDataRng) Is Nothing Then Exit Sub On Error Resume Next If Target.Offset(0, 1) = "" Then Target.Offset(0, 1) = Now End If Target.Offset(0, 2) = Now End Sub @chrisneilsen — Eranga Kasun, Dec 02 '22 at 04:45
You can use the `Worksheet_calculate` event but I can't follow your logic for when a date needs to be added. — Tim Williams, Dec 02 '22 at 04:56

score -1 · Answer 1 · answered Nov 28 '22 at 08:54

IMHO, if you set the concrete range like Set MyDataRng = Range ("A2:A10") Why are you using "Offset" function except of setting the value you need to column B in concrete cell?

Dim a As Long
a=Target.Row
Cells(a,2)= Now

or just so

Cells(Target.Row,2)= Now

Offset is used when you have dynamic source range. And when you have exact Target range, you can write an offset address from Target

Date doesn’t auto fill when the cell updates data automatically but manually when I change data it works well

1 Answers1