It works perfectly in my workbook.
Row 6 is unhighlighted in sheet1
and 6 and 13 are unhighlighted in sheet2.
better check to make sure the macro was copied correctly,
here it is from my workbook:
Option Explicit
Public Dict_Data1, Dict_Data2
Sub Compare()
Dim Sht1, Sht2
Dim nRows, r, sData, rArray, inx
Dim tstart, tstop, TElapsed, TMin, TSec, msg
'---------------------------------------------------------
Application.ScreenUpdating = False
'---------------------------------------------------------
tstart = Timer
Set Dict_Data1 = CreateObject("Scripting.Dictionary")
Set Dict_Data2 = CreateObject("Scripting.Dictionary")
Dict_Data1.RemoveAll
Dict_Data2.RemoveAll
'---------------------------------------------------------
'Identify sheets to compare
'---------------------------------------------------------
Sht1 = "Sheet1"
Sht2 = "Sheet2"
'---------------------------------------------------------
' Clear Highlighting
'---------------------------------------------------------
Sheets(Sht1).Select
With Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Sheets(Sht2).Select
With Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'---------------------------------------------------------
' Load data from Sht1 into Dictionary Object
'---------------------------------------------------------
nRows = Application.WorksheetFunction.CountA(Sheets(Sht1).Range("A1:A" &
Sheets(Sht1).Rows.Count))
For r = 2 To nRows
If (r Mod 1000 = 0) Then Application.StatusBar = "Loading " & Sht1 & "
row " & r & " of " & nRows
sData = Trim(Sheets(Sht1).Cells(r, "A").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "B").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "C").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "D").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "F").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "H").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "I").Value)
If (Not Dict_Data1.exists(sData)) Then
Dict_Data1.Add sData, r
'--------------------------------------------------------------
Else ' In case of duplicate rows, collect all row numbers
'--------------------------------------------------------------
Dict_Data1.Item(sData) = Dict_Data1.Item(sData) & "," & r
End If
Next
'---------------------------------------------------------
' Process Sht2 data, compare to sht1
'---------------------------------------------------------
nRows = Application.WorksheetFunction.CountA(Sheets(Sht2).Range("A1:A" &
Sheets(Sht2).Rows.Count))
For r = 2 To nRows
If (r Mod 1000 = 0) Then Application.StatusBar = "Processing " & Sht2 &
" row " & r & " of " & nRows
sData = Trim(Sheets(Sht2).Cells(r, "A").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "B").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "C").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "D").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "F").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "H").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "I").Value)
If (Dict_Data1.exists(sData)) Then
'-----------------------------------
'highlight row in sht2
'-----------------------------------
Sheets(Sht2).Select
With Sheets(Sht2).Range(Cells(r, "A"), Cells(r, "I")).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'-----------------------------------
'highlight rows in sht1
'-----------------------------------
Sheets(Sht1).Select
rArray = Split(Dict_Data1.Item(sData), ",")
For inx = 0 To UBound(rArray)
With Sheets(Sht1).Range(Cells(rArray(inx), "A"),
Cells(rArray(inx), "I")).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Next inx
End If
Next
Application.StatusBar = False
Application.ScreenUpdating = True
'----------------------------------------------------
msg = "Finished: "
tstop = Timer
TElapsed = tstop - tstart
TMin = 0
TMin = TElapsed \ 60
TSec = TElapsed Mod 60
msg = msg & Chr(13) & Chr(13)
If (TMin > 0) Then msg = msg & TMin & " mins "
msg = msg & TSec & " sec"
'----------------------------------------------------
MsgBox msg
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
>________________________________
> From: Missy786 <misbah.ali...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Friday, April 25, 2014 9:08 AM
>Subject: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro
>
>
>
>Hi Paul,
>
>
>Thank you so much for your time and effort into problem. The code is very
>well structured, but I am sorry to inform, that code highlighted every row in
>sheet1, as the correct output would be highlighting every row in sheet1
>besides row 6, as its not the same as sheet2 row6.
>
>
>I am sorry to cause trouble. Is this problem easy to fix?
>
>
>I really appreciate your time and help.
>--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
