Highlighting Duplicates in a List

[John Mansfield]

I'm using the following code to highlight duplicate data
contained in two lists. Unfortunately both lists are huge
so it's taking the macro a long time to run and is using a
lot of memory. Would anyone have any advice on how the
code could be modified to run more efficiently? My
thought was that maybe the data could be loaded into an
array but I am not yet proficient with how to handle VBA
arrays. Thanks for your help.

Sub FindDuplicates()
Application.ScreenUpdating = False
Dim Rng1 As Range
Dim Rng2 As Range
Dim x As Variant
Dim y As Variant
Set Rng1 = Range("B7:B15000")
Set Rng2 = Range("C7:C15000")

For Each x In Rng1
For Each y In Rng2
If x = y Then
x.Interior.ColorIndex = 6
y.Interior.ColorIndex = 6
End If
Next y
Next x
Application.ScreenUpdating = True
End Sub

 

[JWolf]

Have you tried conditional formatting instead?

Format-->Conditional Format-->Formula is:
=IF(COUNTIF($C$7:$C$15000, B7)>1,TRUE,FALSE)

 

[JWolf]

Make that >0 since you are looking for duplicates in different columns.