Copy cell text if background color is red RRS feed

  • Question

  • Wondering if anyone knows how to write a program that looks through 7 sheets and copies cell text if their background color is red.

    The overall goal would then be to put all the cell text into a running list and then paste it into a column on a different worksheet.

    Monday, August 8, 2016 7:46 PM

All replies

  • Try this macro:

    Sub CopyData()
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim rngC As Range
        Dim strA As String
        Dim lngR As Long
        Application.ScreenUpdating = False
        With Application.FindFormat
            .Interior.Color = vbRed
        End With
        Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        For Each wshS In Worksheets
            If wshS.Name <> wshT.Name Then
                With wshS.Cells
                    Set rngC = .Find(What:="", SearchFormat:=True)
                    If Not rngC Is Nothing Then
                        strA = rngC.Address
                            lngR = lngR + 1
                            wshT.Range("A" & lngR).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                            Set rngC = .Find(What:="", After:=rngC, SearchFormat:=True)
                        Loop Until rngC.Address = strA
                    End If
                End With
            End If
        Next wshS
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar (

    Monday, August 8, 2016 9:37 PM
  • Great work,

    If I was to want that copied information pasted on a preexisting sheet. How would I do that?

    Thank you.

    Monday, August 8, 2016 11:30 PM
  • Set the value of wshT to the pre-existing worksheet and wshT.Range to the location on that sheet where you want the data.

    Graham Mayor - Word MVP

    Tuesday, August 9, 2016 3:26 AM