Group,
Lets assume I have two worksheets within the same workbook. Let
also assume that I have approximately 30 Columns of data with 100 rows
The names in column 1 for the most part rarely changes. If they do
its because a row has either been added or deleted somewhere in th
respective spreadsheets. The names in Column 1 are always unique. S
comparing would be, find a name in Sheet 1 Column 1, next look for tha
same name in Sheet 2, Column 1. If there is a match, then compare eac
row for differences. If there is a change, alter the color of the cel
in both worksheets. If a row has been added and doesn't appear in bot
worksheets, highlight the row with the addition. If a row is delete
highlight the row in the previous worksheet not deleted. For a ver
compact comparison routine, would VLOOKUP be my choice to determin
changes? If not, what could satisfy my requirements?

Ton

--
ajociu
-----------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...fo&userid=1769
View this thread: http://www.excelforum.com/showthread.php?threadid=39484

Hi,
MATCH or VLOOKUP:

Sub MatchCol()


Dim rng1 As Range, rng2 As Range, res

With Worksheets("sheet1")
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng1 = .Range("a2:a" & Lastrow)
End With

With Worksheets("sheet2")
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = .Range("a2:a" & Lastrow)
End With

For Each cell In rng1

res = Application.VLookup(cell, rng2, 1, False) ' Lookup cell in Rng1 with
Rng2
'
' OR
'
res = Application.Match(cell, rng2, 0) ' Match cell in Rng1 with Rng2

If Not IsError(res) Then ' Matched i.e.
'
' Compare rows .....
'
Else

' Highlight row in Sheet1 i.e. it is missing in Sheet2
End If
Next cell
End Sub

"ajocius" wrote:


Group,
Lets assume I have two worksheets within the same workbook. Lets
also assume that I have approximately 30 Columns of data with 100 rows.
The names in column 1 for the most part rarely changes. If they do,
its because a row has either been added or deleted somewhere in the
respective spreadsheets. The names in Column 1 are always unique. So
comparing would be, find a name in Sheet 1 Column 1, next look for that
same name in Sheet 2, Column 1. If there is a match, then compare each
row for differences. If there is a change, alter the color of the cell
in both worksheets. If a row has been added and doesn't appear in both
worksheets, highlight the row with the addition. If a row is deleted
highlight the row in the previous worksheet not deleted. For a very
compact comparison routine, would VLOOKUP be my choice to determine
changes? If not, what could satisfy my requirements?

Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=394841