Two dynamic lists on two sheets. Check for and identify duplicates. How?

[Mel Smith]

I have two dynamic lists of names, both in column B on sheets "First Class" and "T20". What I would like to do is run a macro(?) on sheet "T20" that will check the list of names on the "First Class" sheet and highlight any duplicates in there might be in column B on the "T20" sheet.

How may I achieve this, please?

Mel

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
    Set srcWS = Sheets("T20")
    Set desWS = Sheets("First Class")
    v1 = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Value
    v2 = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v2) To UBound(v2)
        If Not dic.exists(v2(i, 1)) Then
            dic.Add v2(i, 1), i + 1
        End If
    Next i
    For i = LBound(v1) To UBound(v1)
        If dic.exists(v1(i, 1)) Then
            desWS.Range("B" & dic(v1(i, 1))).Interior.ColorIndex = 3
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Skyybot]

Try this on a copy of your Workbook as unexpected results may occur. Place code in ThisWorkbook of your VBE

Sub FindDuplicates()
Dim wb As Workbook, sht1 As Worksheet, sht2 As Worksheet, srchRng As Range, fndRng As Range, cell As Range
Dim lRowSht1 As Integer, lRowSht2 As Integer, m As Range
Set wb = ThisWorkbook: Set sht1 = wb.Sheets("First Class"): Set sht2 = wb.Sheets("T20")
lRowSht1 = sht1.Columns("B").Rows.End(xlDown).Row: lRowSht2 = sht2.Columns("B").Rows.End(xlDown).Row
Set fndRng = sht1.Range("B2:B" & lRowSht1): Set srchRng = sht2.Range("B2:B" & lRowSht2)
For Each cell In srchRng
    Set m = fndRng.Find(cell.Value, LookAt:=xlWhole)
    If Not m Is Nothing Then
        cell.Interior.Color = 65535
    End If
Next cell
End Sub

 

[Mel Smith]

Thank you Mumps, that really is a great help!

Mel

 

[mumps]

You are very welcome.