VBA - VLookup ?

[ajocius]

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

 

[Guest]

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