Macro to Find Duplicate Values in Two Workbooks

Hi beewketu,

Try this:

Sub beewketu(): Dim r As Long, wa As Worksheet, wc As Worksheet
                Dim TID As Object, ID As String, D As Range
            Set wa = Workbooks("Archive").Sheets("Sheet1")
            Set TID = CreateObject("Scripting.Dictionary")
            Set wc = Workbooks("Current File").Sheets("Sheet1"):
            For r = 3 To wa.Range("A" & Rows.Count).End(xlUp).Row
            ID = Trim(wa.Range("A" & r)): TID.Item(ID) = r: Next r
            For r = 3 To wc.Range("A" & Rows.Count).End(xlUp).Row
                    ID = Trim(wc.Range("A" & r))
                    If TID.Exists(ID) Then
           If D Is Nothing Then Set D = wc.Range("A" & r) Else _
            Set D = Union(D, wc.Range("A" & r))
            End If: Next r
            
            If D Is Nothing Then
            MsgBox "No dupes exist"
            Else
            D.EntireRow.Interior.ColorIndex = 6: End If
End Sub