[RESOLVED] highlight duplicate rows include original instance.

[abhay_547]

I want all the duplicate rows highlighted in a worksheet along with original instance e.g. if I have row 1 with some data in multiple columns and the same date appears on row 20 then the macro should highlight both the row items in some same color. I got the below code through google search but when it try to run it gets stuck on the below line and shows error Run time Error '13' type mismatch. Excel version used by me is excel 2010. can someone help.

NewStr1 = NewStr1 & "||" & ws1.Cells(Row1.Row, Col1.Column) ' it's throwing runtime error on this line.

Option Explicit
Sub HighDupes()
Dim ws1 As Worksheet
Dim Row1 As Range
Dim Col1 As Range
Dim NewStr1 As String
'Needs reference to Microsoft Scripting Runtime
Dim MyDic As Dictionary
Set MyDic = New Dictionary
Set ws1 = ActiveWorkbook.Sheets(1)
Application.ScreenUpdating = False
For Each Row1 In ws1.UsedRange.Rows
'If rows are blank then skip
If Application.CountA(ws1.Rows(Row1.Row)) > 0 Then
NewStr1 = "Sheet1"
For Each Col1 In ws1.UsedRange.Columns
NewStr1 = NewStr1 & "||" & ws1.Cells(Row1.Row, Col1.Column) ' it's throwing runtime error on this line.
Next
If MyDic.exists(NewStr1) Then
'Colour intra sheet duplicates in sheet 1 as blue
ws1.Rows(Row1.Row).Interior.Color = vbBlue
ws1.Rows(MyDic(NewStr1)).Interior.Color = vbRed
Else
MyDic.Add NewStr1, Row1.Row
End If
End If
Next
Application.ScreenUpdating = True
Set MyDic = Nothing
Set ws1 = Nothing
End Sub