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
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