excelvbadatetimeauto

Automatically shows date and time in VBA on multiple columns


As on topic, I want my excel formula automatically shows date and time after key-in it's data on respective cell. I am able to get date and time on column A & B while key-in data on column C. Below is my VBA Code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Input")) Is Nothing Then
Application.EnableEvents = False
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    Range("MyList").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria"), Unique:=False
    Application.EnableEvents = True
    End If
    
    If Target.Column <> 3 Then Exit Sub
    Application.EnableEvents = False
        If Target.Offset(0, -2).Value = "" And Target.Offset(0, -2).Value = "" Then
            Target.Offset(0, -2).Value = Date
            Target.Offset(0, -1).Value = Time
        End If
Application.EnableEvents = True
End Sub

I want same things on Column I & J while key in data on column K and others also as on the screenshot

as below photo


Solution

  • Personal preference, but I would use Select over If-statement for this scenario to make it a bit more clean.

    It also makes for easier modifications later, if necessary.

    Select Case Target.Column
        Case 3, 11
            Application.EnableEvents = False
            If Target.Offset(0, -2).Value = "" And Target.Offset(0, -2).Value = "" Then
                Target.Offset(0, -2).Value = Date
                Target.Offset(0, -1).Value = Time
            End If
        Case Else
            Exit Sub
    End Select