Search Two Ranges To See If The Same String Exists

[snb]

Another caveat: avoid 'select' and 'activate' in VBA

probably some more speed gain, using

Sub tst4()
    t = Timer
    Dim sq
    sn = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    sp= Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls").Sheets("Weekly Shipped Details").Range("I5:I1000")
    ReDim sq(1 To UBound(sn))

    For i = 1 To UBound(sn)
        sq(i) = IIf(IsError(Application.Match(sn(i, 1), sp,0)), "#N/A" ,sn(i, 1))
    Next
    Range("C2").Resize(UBound(sn)) = WorksheetFunction.Transpose(sq)

    MsgBox Timer - t
End Sub