Compare and Highlight Rows

[Guest]

I have an excel file with two worksheets. is there a way to programatically
compare the two worksheets to find matches and highlight those matches on
each worksheet.

They both have the same columns.
I want to compare the InspectionID column.
The Inspection ID column may have duplicates in both of the worksheets.

I have never done anything in excel above the beginner level. However, I am
a very skilled VBA programmer (programmed in ACCESS for over 10 years).

Thank you

 

[JE McGimpsey]

You may find it easier to use Conditional formatting instead:

For instance, with Sheet1 column A selected, and cell A1 active,
checking against column B in sheet2:

Format/Conditional Formatting...

CF1: Formula Is =COUNTIF(Sheet2!B:B,A1) > 0
Format1: <pattern>/<color>

Do something similar with CF in Sheet2, column B.

 

[Bernard Liengme]

Not sure this works. CF does not allow reference to other worksheets
You could put the formula in a cell on Sheet 1 and use CF to look at its
value.
best wishes

 

[Guest]

What do you think about this code. I think this may work if I knew the code
for highlighting a row

Sub Find_MatchesINZips()

Dim compareRange As Variant
Dim x As Variant, y As Variant

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

For Each x In Selection
For Each y In compareRange
If x = y Then Highlighte Row ***(Need code here)***
Next y
Next x

End Sub

 

[JE McGimpsey]

Actually, I'm positive it doesn't, at least the way I posted. I tried to
simplify, and forgot about the reference. Thanks for the correction,
Bernard!


what DOES work is to NAME the columns in each sheet. For instance, name
Column B in sheet2, say, "ID2"

Then in Sheet1, column A, use

CF1: Formula is =COUNTIF(ID2, A1)
Format1: <pattern>/<red>

 

[Dave Peterson]

Just a warning about the name ID2--it looks way too much like a cell address
(when in A1 reference style).

How about _ID2
(with a leading underscore)

 

[JE McGimpsey]

OK, I'm officially too distracted to be posting here. I'm going back to
just working...


Thanks for the correction, Dave.

 

[Guest]

Please, I am so close. I know this would work if I knew the syntax for
accessing the current row and highlighting it.

Sub Find_MatchesINZips()

Dim compareRange As Variant
Dim x As Variant, y As Variant

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

For Each x In Selection
For Each y In compareRange
If x = y Then x.Rows.BackColor = vbYellow ****x.rows is not
correct****
Next y
Next x

End Sub

 

[Guest]

PLEASE HELP - Using the following code I am getting the following error

Unable to set the pattern property of the interior class

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

 

[Peo Sjoblom]

Why swim the river to get to the water, you have been given a solution using
conditional formatting?


--
Regards,

Peo Sjoblom

 

[Guest]

Because I know there is always more then one way to skin a cat. That is what
makes me a great programmer. By the way, here is the solution.
------------------------------------
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).EntireRow.Interior.ColorIndex
= 6
Next y
counter = counter + 1
Next x

 

[JE McGimpsey]

Except that your solution is less efficient, non-automatic, and will not
work for users who disable macros.

Given your original problem statement, it seems to me a real
disadvantage that you rely on the inspectionID column on Worksheets(1)
being selected rather than specifying it in your macro.

Your macro also doesn't meet your criterion of "highlight(ing) those
matches on each worksheet", as it will only highlight the rows on sheet
1.

As a great programmer, you might also recognize that using variants is
rather inefficient compared to using range objects or simple data types.
And one should generally avoid using the Integer data type for rows,
since Integers are limited to +32,767 and the number of rows isn't.

And, finally, the VBA comparison is likely much less efficient than
using a built-in function, say:

Const idCol1 As Long = 1 'worksheet 1 ID column
Const idCol2 As Long = 2 'worksheet 2 ID column
Dim compareRange as Range
Dim rCell As Range

With Worksheets(2)
Set compareRange = .Range(.Cells(2, idCol2), _
.Cells(.Rows.Count, idCol2).End(xlUp).Row)
End With
With Worksheets(1)
For Each rCell in .Range(.Cells(2, idCol1), _
.Cells(.Rows.Count, idCol1).End(xlUp).Row)
With rCell
If Application.WorksheetFunction.CountIf( _
compareRange, .Value) > 0 Then _
.EntireRow.Interior.ColorIndex = 6
End With
Next rCell
End With

 

[Evgeny Grischenko]

Hello!
please, try one useful add-in that i usually use for comparing spreadsheets:
http://www.office-excel.com/excel-addins/compare-spreadsheets.html

Regards,
Eugene