I found this VBA code which presently fills cols E and F with Date/Time and updates col F only with Date/Time when it detects a change in the range A2:D50. It works beautifully and thanks to the author. What I'd like to do is:

  • to fill col E if there's a change in range B2:B50.
  • and fill col F if there's a change in range D2:D50.

I tried to modify the code and could fill col E, but am unable to update col F.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Timestamp Data
    '   TeachExcel.com - Original Author

    Dim myTableRange As Range
    Dim myDateTimeRange As Range
    Dim myUpdatedRange As Range

    'Your data table range
    Set myTableRange = Range("A2:D50")

    'Check if the changed cell is in the data tabe or not.
    If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

    'Stop events from running
    Application.EnableEvents = False

    'Column for the date/time
    Set myDateTimeRange = Range("E" & Target.Row)
    'Column for last updated date/time
    Set myUpdatedRange = Range("F" & Target.Row)

    'Determine if the input date/time should change
    If myDateTimeRange.Value = "" Then   
        myDateTimeRange.Value = Now 
    End If

    'Update the updated date/time value
    myUpdatedRange.Value = Now

    'Turn events back on
    Application.EnableEvents = True
End Sub

1 Answers

1
Zac On

Set myTableRange to B2:B50. Then change your IF statement to: If Not Intersect(Target, myTableRange) Is Nothing Then. If Target is in range, perform your steps. If Target is not in that range, reset myTableRange to D2:D50 in an Else. Then check if your Target is in that range. If it is, perform your steps