Date and Time stap for Part In & Part Out

[crystal2000a]

Hi,

I'm trying to track down a way to write a code to enter date/Time [NOW()] when a part is scanned in and out. I however want to place the time in within column C and time out within column D. This would mean:

'Active column is B
'When Part A gets scanned into B3, a Date/Time in is captured in C3.
'When Part A gets scanned out, it looks up (Vlookup maybe?) a duplicate of the part number (B3) and places a Date/Time out in D3
'After that, active cell moves to E3, for operator to enter a 1 or 2 value (for a Pass (1)/Fail (2) criteria for scratches.
'When Part B gets scanned in, and looks up a duplicate in Column B and if none found, would place the part number in B4 with a Date/Captured in C4.


This is as far as I've gone:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim sInputE As String

If Target.Cells.Count > 1 Then Exit Sub

If Target.Column = 2 Then
Application.EnableEvents = False
'* Set Date/Time value in column C
With Target.Offset(, 1)
.Value = Now
.EntireColumn.AutoFit
End With


Do While sInputE = ""
sInputE = InputBox("Scratch Failure" & vbCrLf & "(1 for Pass, 2 for Fail)", "Input Data")
If sInputE <> "" Then
If MsgBox("Please confirm " & sInputE & " is correct.", vbYesNo) = vbYes Then
Target.Offset(0, 3) = sInputE
Else
sInputE = ""
End If
End If
Loop

Target.Offset(1).Select
Application.EnableEvents = True
End If

End Sub


Please help! Thanks a mil!!!