Comparing Two Large Lists - Highlighting Duplicates

[trizahler]

Hello Everyone,

I am having difficulty in identifying and highlighting exact number matches between two lists.

If this question has been answered could someone point me to the appropriate forum post ?

Thank you everyone for your time.

https://docs.google.com/spreadsheets...it?usp=sharing

 

[AlanSidman]

Option Explicit

Sub dupe()
    Dim lrA As Long, lrB As Long
    lrA = Range("A" & Rows.Count).End(xlUp).Row
    lrB = Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long, j As Long
    Application.ScreenUpdating = False
    For i = 2 To lrA
        For j = 2 To lrB
            If Range("A" & i) = Range("B" & j) Then
                Range("A" & i).Interior.ColorIndex = 4
                Range("B" & j).Interior.ColorIndex = 4
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "Complete"
End Sub

 

[Hui]

You can use a Conditional Format to highlight the Duplicates

Here's an example


Select the range C3:C7
Goto the Home, Conditional Formatting, New Rule...
Use a Formula
=COUNTIF($B$3:$B$7,$C3)=1
Select a Format and Apply

 

[AlanSidman]

Just a point of interest. Hui's solution highlights only the duplicate values in column C. My VBA solution highlights the duplicates in both Columns B and C. Both are correct solutions. The OP should choose the one that suits/meets his requirements.

 

[trizahler]

Thank you gentlemen, I appreciate your efforts. Both of these worked well!

My Best,

Tom