Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$A$2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Dim LRow As Long, LRowX As Long
Dim aRng As Range, rngFnd As Range, rngFndX As Range
Dim myFnd As String, myFndX As String
myFnd = Target
myFndX = Target.Offset(, 1)
If myFnd = "" Then
Exit Sub
ElseIf IsNumeric(myFnd) Then
myFnd = Val(myFnd) '/ converts a "text" number to a value
Else
'/ is text and that is okay
End If
With Sheets("Data Input")
LRow = Sheets("Data Input").Cells(Rows.Count, "A").End(xlUp).Row
Set rngFnd = Sheets("Data Input").Range("A2:A" & LRow).Find(What:=myFnd, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rngFnd Is Nothing Then
rngFnd.Resize(1, 6).Copy Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp)(2)
Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 6) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
rngFnd.Offset(, 1).Resize(1, 5).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp)(2)
Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 6) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
Else
MsgBox "No " & myFnd & " match found."
End If
LRowX = Sheets("Data Input").Cells(Rows.Count, "N").End(xlUp).Row
Set rngFndX = Sheets("Data Input").Range("N3:N" & LRowX).Find(What:=myFndX, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rngFndX Is Nothing Then
rngFndX.Resize(1, 2).Copy Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp)(2)
rngFndX.Offset(, 1).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 5)
Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = Format(Now(), "yyyy-MM-dd hh:mm:ss")
Else
MsgBox "No " & myFndX & "match found."
End If
End With
[A2].Activate
End Sub
