Highlighting Duplicates in a List
 

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

Highlighting Duplicates in a List
 

Have you tried conditional formatting instead?

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

John Mansfield wrote:
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

Highlighting Duplicates in a List
 

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

JWolf wrote:

Have you tried conditional formatting instead?

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

John Mansfield wrote:

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