Here is another approach you can try...

Sub Marine()
Dim WS1 As Worksheet, WS2 As Worksheet, R As Range, C As Range
Dim StartAt As Range, X As Long, StartRow As Long, StartCol As Long
Dim LR1 As Long, LR2 As Long, GreaterThanAmount As Long
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
StartRow = 3
StartCol = 1
GreaterThanAmount = 5
On Error Resume Next
LR1 = WS1.Cells(WS1.Rows.Count, StartCol).End(xlUp).Row
For X = 2 To LR1
Set StartAt = WS1.Cells(X, StartCol)
Set R = WS2.Rows(StartRow).Find(StartAt.Value, _
LookAt:=xlWhole, MatchCase:=False)
If Not R Is Nothing Then
LR2 = R.EntireColumn.Find("*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
Set C = WS2.Cells(LR2, R.Column)
If IsNumeric(C.Value) Then
If C.Value GreaterThanAmount Then StartAt.Value = 1
End If
End If
Next
End Sub

--
Rick (MVP - Excel)


"stumpy" wrote in message
...
I am trying to match numbers in sheet1 column A to rows in sheet2 (row
3),then if there is a match find the last number in the column that
matches(sheet2),if that is above 5 then enter 1 into sheet1 column b next
to
the value that has been matched

eg
sheet1!A1 = 123 (needs to be matched in sheet2)
sheet2!C3 = (matched number) 123, find last value in column c
if last value in column c is greater than 5 then
sheet1!B1 = 1
then loop through all rows in Sheet1 column A

any help is greatly appreciated