Hi Colin,

Am Wed, 2 May 2012 22:22:00 +0100 schrieb Colin Hayes:

Well I'm experimenting with both. The macro causes a long pause while it
processes. The formula works too. Both very good. I could probably build
the formula into a macro too so it's all good.

this macro should be faster. There are no nested for each..next
Please test it in a copy of your workbook:
Sub Checking()
Dim LRow1 As Long
Dim LRow2 As Long
Dim rngC As Range
Dim myFind As Long

With Sheets("Sheet2")
LRow1 = .Cells(.Rows.Count, 2).End(xlUp).Row
LRow2 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Sheets("Sheet1").Range("A2:A" & LRow2)
If WorksheetFunction.CountIf(.Range("B1:B" & LRow2), rngC) 0 Then
myFind = WorksheetFunction.Match(rngC, .Range("B1:B" & LRow1), 0)
If .Cells(myFind, "H") = 3 Or _
.Cells(myFind, "H") .Cells(myFind, "J") Then
rngC.Offset(0, 4) = .Cells(myFind, "J")
End If
End If
Next rngC
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2