comparing 2 tabs and returning matched data

[ahs004]

Hi all,

I have coded the below but it doesnt seem to be working.

It returns the values and rows but its not the same same data when i cross reference it.

The data i am comparing is unique reference number that differs from 3 digit to a 6 digit. i think it matches the first one that finds which contains that number. ie; if 33405 and returs 3405.

Is there a way that i can set it to return value on exact match?

Many thanks with your help.


Sub MatchResidents_martlet()
Dim i As Long, k As Long, n As Variant, r As Range
Application.ScreenUpdating = False
With Sheets("Martlet")
Set r = Range(.Cells(7, 18), .Cells(65536, 18).End(xlUp))
End With
k = 0
i = 1
While Not IsEmpty(Sheets("Sheet1").Cells(i, 7))
n = Application.Match(Sheets("Sheet1").Cells(i, 7).Value, r, 0)
If IsNumeric Then
Sheets("Sheet1").Cells(i, 7).Interior.ColorIndex = 35
k = k + 1
Sheets("Martlet").Rows.Copy Sheets("Sheet2").Rows(k)
Else
Sheets("Sheet1").Cells(i, 7).Interior.ColorIndex = 3
End If
i = i + 1
Wend
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub