Compare two worksheets and Highlight duplicates in each

[Lisab]

PLEASE HELP!
I am trying to highlight every row in a worksheet that has a duplicate in
another worksheet in the same excel file.

I have never programmed in excel but I am very skilled at VBA.

I am using the following code. However, it errors out in the IF THEN
statement.

***Unable to set the Pattern property of the interior class**

I also tried using the ColorIndex property and I get the same error-
Selection.Rows(counter).Interior.ColorIndex = vbYellow
---------------
Sub Find_MatchesINZips()

Dim compareRange As Variant
Dim x As Variant, y As Variant
Dim counter As Integer

Set compareRange = Worksheets(2).Range("A2:A149")
counter = 1

For Each x In Selection
For Each y In compareRange
If x = y Then Selection.Rows(counter).Interior.Pattern = vbYellow
Next y
counter = counter + 1
Next x

End Sub

 

[JW]

If x = y Then Selection.Rows(counter).EntireRow.Interior.ColorIndex =
6

 

[Lisab]

Perfect! Thank You Very Much!

If x = y Then Selection.Rows(counter).EntireRow.Interior.ColorIndex =
6

 

[Jim Thomlinson]

Give this a whirl...

Sub Find_MatchesINZips()

Dim compareRange As Range
Dim x As Range, y As Range

Set compareRange = Worksheets(2).Range("A2:A149")

For Each x In Selection
For Each y In compareRange
If x.Value = y.Value Then y.EntireRow.Interior.ColorIndex = 6
Next y
Next x

End Sub

 

[Bill Renaud]

I added some variables to your code (it makes debugging much easier),
and used a Find method, which should run much faster than iterating
through each cell in the Compare range with a For loop. I also used the
Color property, instead of the ColorIndex property, which might not be
yellow, if the color pallette has been changed.

Sub FindMatchesInZips()
Dim rngCell As Range
Dim rngSelection As Range
Dim rngCompare As Range

Set rngSelection = Selection
Set rngCompare = Worksheets(2).Range("A2:A149")

For Each rngCell In rngSelection
If Not (rngCompare.Find(What:=rngCell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows) Is Nothing) _
Then
rngCell.EntireRow.Interior.Color = vbYellow
End If
Next rngCell
End Sub

 

[Lisab]

Thank You ALL, I love having more than one way of doing things.

What if I only wanted to highlight columns A: through I: and not the entire
row, what would be the syntax

 

[Lisab]

Can I add an AND to the code
I I take your code and modify it as follows, that highlight the rows in both
Sheet 1 and sheet 2?

Dim compareRange As Range
Dim x As Range, y As Range

Set compareRange = Worksheets(2).Range("A2:A149")

For Each x In Selection
For Each y In compareRange
If x.Value = y.Value Then y.EntireRow.Interior.ColorIndex = 6, _
AND x.entireRow.Interior.ColorIndex = 6
Next y
Next x

 

[Jim Thomlinson]

And is a logical operator so that will not quite work out... try this...

Dim compareRange As Range
Dim x As Range, y As Range

Set compareRange = Worksheets(2).Range("A2:A149")

For Each x In Selection
For Each y In compareRange
If x.Value = y.Value Then
y.EntireRow.Interior.ColorIndex = 6
x.entireRow.Interior.ColorIndex = 6
End If
Next y
Next x

 

[Bill Renaud]

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

 

[Lisab]

Bill, thank you for all your help. I really appreciate it.

just as a side note- I tried using vbYellow but that is what caused my error

 

[Bill Renaud]

Lisab wrote:
<<I tried using vbYellow, but that is what caused my error on my first
couple of attempts. That is why I changed "vbYellow" to "6">>

I think you were using the ColorIndex or Pattern property originally,
which requires a Variant data type for the argument. If the value is a
number, then I imagine that the value can only go up to 56 or so,
according to the color table in the Remarks section at the bottom of the
ColorIndex Property topic in Help.

According to the Object Browser, vbYellow has the value of 65,535 and is
therefore too large for the ColorIndex property. This is what produces
the "Run-time error '1004': Unable to set the ColorIndex property of the
Interior class". Setting the value of ColorIndex specifies an "index"
into the default color palette. If a user changes the colors on the
default color palette (Colors tab in the Tools|Options dialog box), then
this color will be some other color.

vbYellow worked in my routine since I used the Color property.