Excel VBA Search For Find Duplicate Values In Two Lists

[Rick Rothstein]

Here is a slightly shorter, non-looping macro that will do what you want as well (just change the assignments in the Const statements I highlighted in red to match your actual setup)...

Sub ListDupes()
  Dim LastRow As Long, List2Address As String
  Const WS As String = "Sheet2"
  Const List1Col As String = "A"
  Const List2Col As String = "D"
  Const OutputCol As String = "E"
  Const StartRow As Long = 2
  LastRow = Worksheets(WS).Cells(Rows.Count, List2Col).End(xlUp).Row
  List2Address = List2Col & StartRow & ":" & List2Col & LastRow
  Application.ScreenUpdating = False
  With Worksheets(WS).Cells(StartRow, OutputCol).Resize(LastRow - StartRow + 1)
    .Cells = Evaluate("IF(COUNTIF('" & WS & "'!" & List1Col & ":" & List1Col & ",'" & WS & _
                      "'!" & List2Address & "),'" & WS & "'!" & List2Address & ","""")")
    On Error Resume Next
    .SpecialCells(xlBlanks).Delete xlShiftUp
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub