Snasui Excel Forum

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim lastRow&
 n = Target.Row
If Cells(n, "b").Value <> "" And Cells(n, "e").Value = "" Then
With Sheets("Tracking")
lastRow = .Range("b" & .Rows.Count).End(xlUp).Row
Cells(n, "e").Value = Date & " " & Time
'Cells(n, "e").NumberFormat = "d/m/yyyy h:mm am/pm"
Cells(n, "c").Value = Application.VLookup(Cells(n, "b"), Sheet1.Range("b:c"), 2, False)
If Application.IsEven(Application.CountIf(.Range("b1:" & "b" & .Rows.Count), Cells(n, "b"))) Then
Cells(n, "d").Value = "เข้า"
Else
Cells(n, "d").Value = "ออก"
End If
  .Range("a" & lastRow) = lastRow - 1
End With
End If
'Range("b:c:e:f").EntireColumn.AutoFit
End Sub