VBA Change: to add time and date stamp only if All required cells are updated & to remove stamp if deleted

  • Hi Im trying to add code so when a user enters data into Cells A, B, C and D the Date and time is populated in Cell E.


    I have a code which actions this request when either of these 4 cells have been changed singularly but was wondering if this could be done if only all 4 Cells have data entered into them and then if deleted from them all the stamp is also deleted.


    If anyone able to help?


    Code:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then


    Application.EnableEvents = False


    Cells(Target.Row, 5).Value = Date + Time


    Application.EnableEvents = True


    End If

    End Sub


    Thanks in advance

  • Hi and Welcome to the Forum :)


    You could test following


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Target.Column > 4 Then Exit Sub
    Application.EnableEvents = False
        If Not IsEmpty(Target) Then
            Cells(Target.Row, 5).Value = Now
        Else
            Cells(Target.Row, 5).ClearContents
        End If
    Application.EnableEvents = True
    End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Thank you for your help.


    It adds/updates the date if any of the cells are updated rather than only adding the date if all the cells in the range 1-4 are updated.


    I think this is the restriction to the change VBA? It seem to be one or another opposed to a group of cells.

  • I have changed the spreadsheet so I require this when one specific cell is updated and will use conditional formating to highlight any issues.


    However I need the date and time added for Two different enties.


    using the code below Ive added the code twice using the different columns. It works for the first one but not the second?


    Not sure where im going wrong....


Participate now!

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