Timestamp in a single cell that updates automatically only when a cell value is CHANGED in a Range on one Sheet

[Excel_User_10k]

Hi All,

I have seen a few things on the internet regarding time stamping when a cell is changed. However, they are always about multiple times stamps for each and every Cell that is changed in one Column, with a corresponding timestamp alongside each cell in another column.

All I wish to do is have a timestamp that only updates in once cell, P3 for example, when a change is made in any of a Range of cells, F8:P439 for instance.

I am hoping to achieve this with a Formula rather than VBA too please. That way, if someone makes a change on a mobile device rather than a PC, the time will still update. It also avoids any errors or instability that almost always seems to follow the use of VBA.

I have also noticed it usually uses the loop error system. Which is fine, but it seems to always require another Column to be used to "copy" the value of the initial Column, and that update is how the timestamp is generated. As I have a rather large table. This would seem impractical and a waste of space. Is it a case of using a Formula and having to do that, or using VBA?

Any ideas please?

 

[6StringJazzer]

First, I made a really serious error. When you use Worksheet change, and your code changes a cell in that worksheet, you must turn off events to prevent infinite recursion. I added the change below.

You would just take my code and add it to your Sub.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngToFltr As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False

   If Not Intersect(Target, Range("F8:P439")) Is Nothing Then
      [P3] = Now
   End If

    If Not Intersect(Target, Range("J4")) Is Nothing Or _
       Not Intersect(Target, Range("G3")) Is Nothing Or _
       Not Intersect(Target, Range("G4")) Is Nothing Then
       Me.Protect Password:="password", AllowFiltering:=True, UserInterfaceOnly:=True

        With Me
            Set rngToFltr = .Range("B7:C" & .Cells(Rows.Count, "B").End(xlUp).Row)
        End With
    
        If Me.FilterMode Then Me.ShowAllData
    
        ' If month selected apply month filter
        If Range("E7") <> "" Then
            With rngToFltr
                .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
            End With
        End If
        
        ' If Store criteria no Invalid ID apply Store filter
        If Range("D6") <> "" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If

    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[Excel_User_10k]

Brilliant, this has worked. Thank You!