Compare and Highlight Rows
 

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

Compare and Highlight Rows
 

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.

In article ,
Lisab wrote:

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

Compare and Highlight Rows
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" wrote in message
...
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.

In article ,
Lisab wrote:

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

Compare and Highlight Rows
 

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


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" wrote in message
...
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.

In article ,
Lisab wrote:

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

Compare and Highlight Rows
 

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




In article ,
"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" wrote in message
...
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.

Compare and Highlight Rows
 

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

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

In article ,
"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" wrote in message
...
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.

--

Dave Peterson

Compare and Highlight Rows
 

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


Thanks for the correction, Dave.

In article ,
Dave Peterson wrote:

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)

Compare and Highlight Rows
 

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



"JE McGimpsey" wrote:

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


Thanks for the correction, Dave.

In article ,
Dave Peterson wrote:

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)

Compare and Highlight Rows
 

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

"Lisab" wrote:

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

Compare and Highlight Rows
 

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


--
Regards,

Peo Sjoblom





"Lisab" wrote in message
...
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



"JE McGimpsey" wrote:

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


Thanks for the correction, Dave.

In article ,
Dave Peterson wrote:

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)

Compare and Highlight Rows
 

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.ColorIn dex
= 6
Next y
counter = counter + 1
Next x


"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom





"Lisab" wrote in message
...
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



"JE McGimpsey" wrote:

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


Thanks for the correction, Dave.

In article ,
Dave Peterson wrote:

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)

Compare and Highlight Rows
 

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

In article ,
Lisab wrote:

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.ColorIn dex
= 6
Next y
counter = counter + 1
Next x


"Peo Sjoblom" wrote:

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

Compare and Highlight Rows
 

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

Regards,
Eugene
"Lisab" wrote in message
...
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