Add message box to "duplicate highlighting" macro

[suremac]

Greetings,

I have a macro (which runs painfully slow) that highlights unique cells between two ranges on different worksheets. I've been trying to add a message box that displays the number of unique cells but the method below has not been working:

Sub HighlightDuplicates()
Application.DisplayAlerts = False


lrU = Sheets("Util").Cells(Rows.Count, 1).End(xlUp).Row
lrPT = Sheets("PivtTable").Cells(Rows.Count, 1).End(xlUp).Row


Dim rng1, rng2, cell1, cell2 As Range
Dim mydiffs As Integer


Set rng1 = Worksheets("Sheet1").Range("DL4:DL" & lrU)
Set rng2 = Worksheets("Sheet2").Range("E3:M" & lrPT)


    For Each cell1 In rng1
    
        For Each cell2 In rng2
            
            If Not cell1.Value = cell2.Value Then
           
            cell2.Interior.Color = vbRed


            mydiffs = mydiffs + 1


            End If
        
        Next cell2
    Next cell1
'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation


Application.DisplayAlerts = True
End Sub

 

[LloydFinancials]

It is working for me and I get the message box correctly. Check the names of your sheets. You have PivtTable instead of PivotTable in the code. Maybe that's it. Otherwise I am not sure. Luke

 

[suremac]

Greetings,

Thanks for the reply. The sheet names are accidentally inconsistent in the code I posted, but they are not in the original code. I keep getting an overflow error.

 

[LloydFinancials]

You can change mydiffs from integer to long. What is happening is if say you have DL4:DL5000, and you have E3:M10000, it is checking the first DL cell against all the EM cells (90,000 each) and then checking the second DL cell against the EM cells and so forth. So in this case, if they are all different, then you have 450,000,000 mydiffs but still there is going to 450,000,000 iterations none the less, hence the long running time.