Problems with a For Each loop

I have the following code:

Sub test()

For Each Cell In Range("A3:A40")

    If Selection.Interior.Colorindex = 34 Then
        Rows(ActiveCell.Row).Select
        Selection.Copy
            Worksheets(1).Select
            Range("A1").Select
            Selection.End(xlDown).Select
            ActiveCell.Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
    End If
    
Next Cell
        
End Sub

This makes sense in my head, but it does not do anything. It runs without error, but it doesn't perform any actions.

There are cell in the range of A3:A40 that contain the interior colorindex 34, but the macro does not copy them, and it doesn't seem to be looping through anything.

For loop is fine, Your if statement is looking at selection. What did you select?

Hmmm - I was thinking that as the loop went through A3:A40, it would go like this:

1.) Select A3
2.) As A3 is selected, look at the selection(a3).Interior.Colorindex
3.) If it's 34, do stuff, if it's not, continue onto A4.
4.) Select A4 & repeat steps 1 through 3.

Should I not be using Selection.Interior.Colorindex? I've also tried with Activecell.Interior.Colorindex and this also does not work.

Originally Posted by Norie

Use the loop variable Cell.

Sub test()

    For Each Cell In Range("A3:A40")

        If Cell.Interior.Colorindex = 34 Then
            Cell.EntireRow.Copy
            Worksheets(1).Range("A" & Rows.Count).End(xlUpDown).Offset(1, 0).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
        End If
    
    Next Cell
        
End Sub

When VBA gets to this line:

Worksheets(1).Range("A" & Rows.Count).End(xlUpDown).Offset(1, 0).PasteSpecial Paste:=xlPasteAllUsingSourceTheme

I get the following error:

Run-time error '1004':

Application-defined or object-defined error.