Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.)

share|improve this question

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
    Set outWS = ActiveWorkbook.Sheets.Add()
    outWS.[A1].Resize(1, UBound(myArray)) = myArray

End Sub
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.