run a macro if a certain cell value changed

[pubudu]

hi
i have wrote following code for get the last modified date of a cell.but the problem is whenever i double click on that cell it changes the date.but i want this macro to run if i changed existing value only.
please help on this.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub

    Target.Offset(0, 1) = Now
    

End Sub

 

[MrKowz]

Welcome to the forums!

One option would be to turn off being able to double-click in a spreadsheet to enter a cell in that column. Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
If target.column = 5 Then
    Cancel = True
End If
End Sub

 

[pubudu]

yeah that works fine.but still if i copy value from function bar it changes the last updated time stamp.

 

[MrKowz]

This feels like a messy fix, but try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurrVal As Variant
Application.EnableEvents = False
If Not Intersect(Target, Range("E:E")) Is Nothing And Target.Count = 1 Then
    CurrVal = Target.Value
    Application.Undo
    If CurrVal <> Target.Value Then
        Target.Value = CurrVal
        Target.Offset(0, 1) = Now
    End If
End If
Application.EnableEvents = True
End Sub

 

[pubudu]

that does it.thank you very much.that is what i exactly needed.

 

[MrKowz]

that does it.thank you very much.that is what i exactly needed.

Glad it works for you. Thank you for the feedback!