Input date when cell changes even if via another referenced cell.

[Lonemascot]

Hi all

This works if the cell is physically changed in column E.

Private Sub Worksheet_Change(ByVal Target As Range)
'add date when status changes
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("e:e"), Target)
xOffsetColumn = -1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd/mm/yyyy"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

However, if the contents of column E are only updated through their own cell references (say on another sheet) then this code doesn't pick up that the contents have changed in column E.

Any ideas to tweak so it does?

 

[Micron]

Use the Calculation event instead? I would step through that code the first time though, to ensure I don't trigger cascading events. It might require disabling events or automatic calculation during execution of the code. If I'm going to turn off events, I usually use an error handler so that events/properties don't get left that way.
Nice that you used code tags, but the lack of indentation still makes it a bit difficult to read/follow.

 

[Lonemascot]

Use the Calculation event instead? I would step through that code the first time though, to ensure I don't trigger cascading events. It might require disabling events or automatic calculation during execution of the code. If I'm going to turn off events, I usually use an error handler so that events/properties don't get left that way.
Nice that you used code tags, but the lack of indentation still makes it a bit difficult to read/follow.

Hey Micron. Thanks for your suggestion.

This was just some code that I found after googling my problem. My VBA knowledge is limited to finding premade snippets and inserting them into my workbooks and hoping for the best. Most of what I do can be done with functions and very, very minor tweaks of code i.e. changing a referenced cell or something simple like that.

As such what you just said, sounded like a foreign language to me

 

[Micron]

If it still is Greek to you, easy enough to Google, right? FYI - the event name often (not always) follows "Private Sub". What you posted is using the WorksheetChange event, which IIRC does not fire when a cell is changed by some other cell, which is what I think you want to happen. Then there is "stepping through the code". If you don't understand that, better to research rather than ask here, because a) it's a bit involved to explain properly and b) you won't have to wait hours/days for a reply.