Autofilter updates the timestamp cell

  • Hi Fellow QzGrid Friends,
    Over a range A1:C100:
    I time stamp the Column B cell when there is any change to the Column A cell.
    * works fine, but
    when I do a filter of Column C Cells Data, the time stamp Column B updates to todays date.
    I want the original timestamp date in Column B to be retained.
    Can anyone help with my problem, thanks.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Not Intersect(Target, Range("A3:C100")) Is Nothing Then
            For Each cell In Target
                cell.Offset(0, 1) = Format(Now(), "ddd, mmm dd, yyyy")
            Next cell
        End If
    End Sub
  • Re: Autofilter updates the timestamp cell


    I'm wondering why you are doing a for each loop?


    You can write the value to each cell at once without using a loop.


    You should also disable events because adding a value to another cell will trigger the event over again.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("B3:B100")) Is Nothing Then
            Application.EnableEvents = False
            Target.Offset(0, 1) = Format(Now(), "ddd, mmm dd, yyyy")
            Application.EnableEvents = True
        End If
    End Sub


    I don't see any auto filter code and to my knowledge auto filter code should not trigger the worksheet change event.


    If it does, it might not be the auto filtering itself, there might be something else happening.

  • Re: Autofilter updates the timestamp cell


    Thanks Skywriter,
    It works fine now.
    Can you please also help with my other problem re having an if / then formula over a range of cells.
    Below I have simple code for looking at cell ("J1") and dong an if / then for a result in ("K1"), BUT
    I want to have this duplicated to look at the range J2 to J10 cells , to give the result in the range K2 to K10 cells as well.
    Should it be a loop code ?

    Code
    Sub Check()
    Dim DDDD As Date, result As String
    DDDD = Range("j1").Value
    If DDDD >= Date Then
        result = "Future"
    Else
        result = "Now"
    End If
    Range("k1").Value = result
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!