Comparing 2 sheets

I have combined two files into one file. I used copy/paste to enter the
contents of file 2 into a new sheet on the original file. I now have a single
file which contains 2 sheets. Let's name the sheets: Source (smaller) and
Original (larger).

Now, I want to use column A containing 419 rows of the Source sheet to search
column A containing 4,763 rows of the Original sheet and highlight the ones
that matching names.

Example: "ABC Industries" is found in column A on both sheets - but in
different rows. On the source sheet, it is in row 5. On the original sheet,
it is in row 20.

I want to use the source sheet "ABC Industries" in row 5 to highlight the
"ABC Industries" in row 20 on the original (as it is a match). However, I
want automate the process of looking at all 419 names in the source sheet to
highlight the matches found in the original sheet.

How can I do this?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1

Joel
Thu May 08 13:46:01 CDT 2008

Sub compare_sheets()


With Sheets("Source")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
Company = .Range("A" & RowCount)
With Sheets("Original")
Set c = .Columns("A").Find(what:=Company, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
c.EntireRow.Interior.ColorIndex = 4
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub


"faureman via OfficeKB.com" wrote:

> I have combined two files into one file. I used copy/paste to enter the
> contents of file 2 into a new sheet on the original file. I now have a single
> file which contains 2 sheets. Let's name the sheets: Source (smaller) and
> Original (larger).
>
> Now, I want to use column A containing 419 rows of the Source sheet to search
> column A containing 4,763 rows of the Original sheet and highlight the ones
> that matching names.
>
> Example: "ABC Industries" is found in column A on both sheets - but in
> different rows. On the source sheet, it is in row 5. On the original sheet,
> it is in row 20.
>
> I want to use the source sheet "ABC Industries" in row 5 to highlight the
> "ABC Industries" in row 20 on the original (as it is a match). However, I
> want automate the process of looking at all 419 names in the source sheet to
> highlight the matches found in the original sheet.
>
> How can I do this?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1
>
>

faureman
Thu May 08 14:10:49 CDT 2008

Thank you very much.

Joel wrote:
>Sub compare_sheets()
>
>With Sheets("Source")
> RowCount = 1
> Do While .Range("A" & RowCount) <> ""
> Company = .Range("A" & RowCount)
> With Sheets("Original")
> Set c = .Columns("A").Find(what:=Company, _
> LookIn:=xlValues, lookat:=xlWhole)
> If Not c Is Nothing Then
> c.EntireRow.Interior.ColorIndex = 4
> End If
> End With
> RowCount = RowCount + 1
> Loop
>End With
>End Sub
>
>> I have combined two files into one file. I used copy/paste to enter the
>> contents of file 2 into a new sheet on the original file. I now have a single
>[quoted text clipped - 15 lines]
>>
>> How can I do this?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1

faureman
Thu May 08 14:25:21 CDT 2008

I'm sorry to trouble you again...

Where do I paste this script in order to be able to execute it?


Joel wrote:
>Sub compare_sheets()
>
>With Sheets("Source")
> RowCount = 1
> Do While .Range("A" & RowCount) <> ""
> Company = .Range("A" & RowCount)
> With Sheets("Original")
> Set c = .Columns("A").Find(what:=Company, _
> LookIn:=xlValues, lookat:=xlWhole)
> If Not c Is Nothing Then
> c.EntireRow.Interior.ColorIndex = 4
> End If
> End With
> RowCount = RowCount + 1
> Loop
>End With
>End Sub
>
>> I have combined two files into one file. I used copy/paste to enter the
>> contents of file 2 into a new sheet on the original file. I now have a single
>[quoted text clipped - 15 lines]
>>
>> How can I do this?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1

Joel
Thu May 08 14:41:03 CDT 2008

Put it in a module sheet in VBA
1) Alt F11
2) Insert Module from VBA menu
3) Paste Code in VBA sheet
4) From worksheet you can execute by using menu Tools - Macro - Macro


"faureman via OfficeKB.com" wrote:

> I'm sorry to trouble you again...
>
> Where do I paste this script in order to be able to execute it?
>
>
> Joel wrote:
> >Sub compare_sheets()
> >
> >With Sheets("Source")
> > RowCount = 1
> > Do While .Range("A" & RowCount) <> ""
> > Company = .Range("A" & RowCount)
> > With Sheets("Original")
> > Set c = .Columns("A").Find(what:=Company, _
> > LookIn:=xlValues, lookat:=xlWhole)
> > If Not c Is Nothing Then
> > c.EntireRow.Interior.ColorIndex = 4
> > End If
> > End With
> > RowCount = RowCount + 1
> > Loop
> >End With
> >End Sub
> >
> >> I have combined two files into one file. I used copy/paste to enter the
> >> contents of file 2 into a new sheet on the original file. I now have a single
> >[quoted text clipped - 15 lines]
> >>
> >> How can I do this?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1
>
>