Compare two worksheets and Highlight duplicates in each
More variables, more variables ... (sigh) (actually, you should love
variables like fruits & vegetables!!! Smile.)
I also used a more advanced technique to "locate" the actual Compare
range on the worksheet (which I named "ReferenceList" in my demo). This
is safer than using a fixed range (i.e.
"Worksheets(2).Range("A2:A149")"). What if the worksheet or the data
change name or size tomorrow? Also, I no longer assume that the
selection is in column $A. I generally use lots of ranges in my code,
because it is very difficult to write and debug otherwise.
Sub FindMatchesInZips()
Dim wsData As Worksheet
Dim rngCell As Range
Dim rngSelection As Range
Dim rngHighlight As Range
Dim wsCompare As Worksheet
Dim rngCompare As Range
Set rngSelection = Selection
Set wsData = rngSelection.Parent
Set wsCompare = Worksheets("ReferenceList")
With wsCompare.UsedRange
Set rngCompare = .Offset(1).Resize(.Rows.Count - 1)
End With
For Each rngCell In rngSelection
If Not (rngCompare.Find(What:=rngCell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows) Is Nothing) _
Then
With rngCell
Set rngHighlight = wsData.Range("A" & .Row & ":I" & .Row)
rngHighlight.Interior.Color = vbYellow
End With
End If
Next rngCell
End Sub
--
Regards,
Bill Renaud
|