Comparing Two Lists of ID Numbers in Excel

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim idRange         As Range
    Dim idCell          As Range
    Dim matchRow        As Long
    Dim rosterSheet     As String
    
    rosterSheet = "Rosters"
    
    Set idRange = Intersect(Range("A:A"), Target)
    If (idRange Is Nothing) Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each idCell In idRange
        If (idCell.Value = vbNullString) Then
            idCell.Offset(0, 1) = vbNullString
            idCell.Offset(0, 2) = vbNullString
            idCell.Offset(0, 3) = vbNullString
        Else
            idCell.Offset(0, 1).Value = Format(Now, "yyyy-mm-dd hh:nn:ss")
            matchRow = getItemLocation(idCell.Value, Sheets(rosterSheet).Columns(1), True, False, True)
            If (matchRow = 0) Then
                idCell.Offset(0, 2) = "** UNKNOWN **"
                idCell.Offset(0, 3) = "** UNKNOWN **"
            Else
                idCell.Offset(0, 2) = Sheets(rosterSheet).Cells(matchRow, "B")
                idCell.Offset(0, 3) = Sheets(rosterSheet).Cells(matchRow, "C")
            End If
        End If
    Next
    Application.EnableEvents = True
End Sub


Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function