Experts Exchange connects you with the people and services you need so you can get back to work.
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)
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
MyDic.Add NewStr1, Row1.Row
Application.ScreenUpdating = True
Set MyDic = Nothing
Set ws1 = Nothing
Open in new window
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.
Premium members get this course for $122.40.
Premium members get this course for $159.20.
Premium members get this course for $299.99.
Premium members get this course for $349.00.
Premium members get this course for $174.99.
Premium members get this course for $148.75.