Extracting colored cells from one spreadsheet into another

[Kari Frey]

I have a spreadsheet color coded with red and green cells (in multiple columns). I need ANOTHER spreadsheet to extract and display just the red cells from the original and place them in a single column. How do I do that?

[Pecoflyer]

Moved to programming section

[NoSparks]

Are the cells colored manually or by conditional formatting ?

What order is required on the new sheet, by row or by column ?

[Kari Frey]

The cells are colored manually. They identify what work still needs to be done (red) vs. what work has been completed (green).

[NoSparks]

You responded too fast.

I edited my original post to add another question.
Should also have asked for the definition of "ANOTHER" spreadsheet and why it was capitalized.

[Kari Frey]

LOL - I just really need an answer!

"Another spreadsheet" is only significant because it's located in a different directory from the original, not just a tab in the same workbook.

The order isn't important, they just all need to be consolidated into 1 column. The original has multiple columns (about 243?) of about 45 rows or less. In the final spreadsheet I just need one column with however many lines it takes to list all of the red cells.

[NoSparks]

Maybe something like this... you'll need to deal with the things you're not telling us.

Sub CopyRedValues()

    Dim cel As Range
    Dim writerow As Long

writerow = 2    

For Each cel In Sheets("source sheet name").UsedRange
    If cel.Interior.Color = vbRed Then
        cel.Copy
        destinationworkbook.Sheets("ANOTHER").Range("A" & writerow).PasteSpecial xlValues
        writerow = writerow + 1
    End If
Next cel

Application.ScreenUpdating = True

End Sub

[Kari Frey]

Thanks so much! I'll massage it and give it a try