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

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

Lisab wrote:
> 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

Perfect! Thank You Very Much!

"JW" wrote:

> If x = y Then Selection.Rows(counter).EntireRow.Interior.ColorIndex =
> 6
>
> Lisab wrote:
> > 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
>
>

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
--
HTH...

Jim Thomlinson


"Lisab" wrote:

> 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

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
--
Regards,
Bill Renaud

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

"Bill Renaud" wrote:

> 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
> --
> Regards,
> Bill Renaud
>
>
>
>

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" wrote:

> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Lisab" wrote:
>
> > 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

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

--
HTH...

Jim Thomlinson


"Lisab" wrote:

> 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" wrote:
>
> > 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
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Lisab" wrote:
> >
> > > 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

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

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
on my first couple of attempts. That is why I changed "vbYellow" to "6"

"Bill Renaud" wrote:

> 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
>
>
>