This formula in Sheet2!A1 and copied down to A10,000 seems to work
instantly on my machine.
=IF(B1=Sheet1!B1,IF(C1=Sheet1!C1,IF(D1=Sheet1!D1,I F(E1=Sheet1!E1,IF(F1=Sheet1!F1,IF(G1=Sheet1!G1,"MA TCH","no"),"no"),"no"),"no"),"no"),"no")
Do you really need VBA to do what you want?

Lawlera wrote:
Hi,

I am trying to compare rows of data and assign a value in col A for matching data. The trouble is the code I have seems to take an age. I have c. 10,000 rows of data in each sheet and if I am to resolve this query before I retire I will need some help!
The code I have is:

Sub loophilite()
Application.ScreenUpdating = False
Dim target, target1, target2 As Variant
Dim target3, target4, target5 As Variant
For a = 1 To 100 'ActiveSheet.UsedRange.SpecialCells(xlLastCell).Ro w
target = Sheets(1).Cells(a, 2).Value
target1 = Sheets(1).Cells(a, 4).Value
target2 = Sheets(1).Cells(a, 5).Value
target3 = Sheets(1).Cells(a, 6).Value
target4 = Sheets(1).Cells(a, 7).Value
target5 = Sheets(1).Cells(a, 8).Value

For b = 1 To Sheets(2).UsedRange.SpecialCells(xlLastCell).Row
If _
target = Sheets(2).Cells(b, 2).Value And _
target1 = Sheets(2).Cells(b, 4).Value And _
target2 = Sheets(2).Cells(b, 5).Value And _
target3 = Sheets(2).Cells(b, 6).Value And _
target4 = Sheets(2).Cells(b, 7).Value And _
target5 = Sheets(2).Cells(b, 8).Value _
Then Sheets(2).Cells(b, 1).Value = b: Sheets(1).Cells(a, 1).Value = b

Next b

Next a
Application.ScreenUpdating = True

End Sub

TIA