I'm having trouble getting an iferror function to work in VBA. The function works fine when entered in the cell itself, but i would like it to run with my sub so i don't have to manually drag down the function. 

Sub If_Red_Copy_to_ALLmissed()
Dim icell As Range, myRange As Range
Dim endcell As Range
Dim index As Long

Set myRange = Union(Range("C3:C21"), Range("H3:H21"), Range("L3:L21"), Range("P3:P21"), Range("T3:T21"))

For Each icell In myRange
    If icell.Interior.Color = RGB(255, 0, 0) Then
        With Sheets("ALLmissed")
            index = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            .Cells(index, 3).Value = icell.Value
            .Cells(index, 4).Value = icell.Offset(0, 1).Value
            .Cells(index, 6).Value = icell.Offset(0, 2).Value
            .Cells(index, 11).Value = Now()
            .Cells(index, 2).Formula = "=WEEKNUM(NOW())-1"
            .Cells(index, 5).Formula = "=IFERROR( VLOOKUP($D2, IDO_Supplier!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D2, Clear_Assy!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D2, reject_fail!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D2, PartQuality!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D2, HFbackend!$D:$E, 2, FALSE), "")))))"
        index = index + 1
        End With
    End If
   
Next icell

End Sub
above is my code. The iferror is looking across 5 other sheets: IDO_supplier, Clear_assy, etc. I would like it to populate in the ALLmissed sheet. when i run it, i am getting an object defined error.

appreciate any help!