I'm not sure what disadvantage there would be to using AutoFilter, but maybe this is more what you were thinking?
Option Explicit
Sub example()
Dim rngFound As Range
Dim rng2Search As Range
Dim lRowLookingAt As Long
With Sheet1 '<--- Use whatever the real sheet's CodeName is
Set rngFound = RangeFound(.Range("A:D"))
If Not rngFound Is Nothing Then
If Not rngFound.Row < 2 Then
For lRowLookingAt = rngFound.Row To 2 Step -1
If .Cells(lRowLookingAt, 4).Value = "Complete" Then
.Cells(lRowLookingAt, 4).Offset(0, -2).Resize(, 2).Value = .Cells(lRowLookingAt, 4).Offset(0, -2).Resize(, 2).Value
End If
Next
End If
End If
End With
End Sub
Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range
If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function
Hope that helps,
Mark