How do I make a list of highlighted cells in Excel?

Question

I have an Excel spreadsheet with about 55 worksheets. Most of the worksheets have 100+ lines. I am creating a list of numbers that meet a specific criteria. I went through the spreadsheet and red-highlighted all the numbers I need. Now I want to make a list of unique values.

I'd like to make some kind of formula/macro/VB Code that goes through each of the 55 worksheets, and lists all of the numbers that are highlighted in red. Is there a way to do that without needing to browse each of the worksheets and copy the red cells, and paste them into a list?

(The next step will be to eliminate the repeated values, but I can probably handle that step.)

Answer 1

Maybe something like this? It will search in all WorkSheet for all cells and rows used. Those that match background being red will be copied in an Array and later ouputted to a new WS. I left the sorting/cleaning repeated values out. It shouldn't be too hard, though. BTW it will output resuts in one row, maybe it's better for you to output it in a column. Probably by creating a 2D array with 1 value per row.

I guess if this can be done using the Application.FindFormat...

Sub FindReds()
    Dim myArray() As Variant
    Dim i As Integer
    Dim outWS As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        For Each Cell In ws.UsedRange.Cells
            If Cell.Interior.ColorIndex = 3 Then
                ReDim Preserve myArray(i)
                myArray(i) = Cell.Value
                i = i + 1
            End If
        Next
    Next
    Set outWS = ActiveWorkbook.Sheets.Add()
    outWS.[A1].Resize(1, UBound(myArray)) = myArray

End Sub