Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
If x = y Then Selection.Rows(counter).EntireRow.Interior.ColorIn dex =
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
Perfect! Thank You Very Much!
"JW" wrote: If x = y Then Selection.Rows(counter).EntireRow.Interior.ColorIn dex = 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two worksheets and Highlight duplicates in each
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. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 5 columns of text and highlight duplicates based on repeti | Excel Worksheet Functions | |||
Compare & highlight changes between two worksheets? | Excel Worksheet Functions | |||
compare two columns within a worksheet, then highlight duplicates | Excel Worksheet Functions | |||
Compare Two Worksheets and Highlight Changes | Excel Programming | |||
Compare two worksheets and highlight the one sheet’s difference from the other one | Excel Programming |