[Excel VBA] How to Extract Only Cells with Background Color

In Excel, background colors are often used to represent status or categories. However, manually extracting only the colored cells can be tedious and limited.

This article introduces how to use VBA to search for “all cells with a background color” and copy them sequentially.

Intended Use Cases

• You want to extract only cells marked with a background color to another sheet or arrange them horizontally.
• You want to programmatically handle data classified by visual appearance.
• You want to aggregate or check data based on colored cells.

VBA Code Example

The following code searches for cells with a background color (fill) within the range D2:F8 and copies them sequentially to the right, starting from cell J2.

Sub ExtractColoredCells()

    Dim searchArea As Range
    Dim foundCell As Range
    Dim firstFound As Range
    Dim pasteCell As Range

    ' Define the search range
    Set searchArea = ActiveSheet.Range("D2:F8")
    ' Define the starting destination cell
    Set pasteCell = ActiveSheet.Range("J2")

    ' Initialize and set search criteria (target cells with solid fill)
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Pattern = xlPatternSolid

    ' Find the first matching cell
    Set foundCell = searchArea.Find(What:="", SearchFormat:=True)

    If foundCell Is Nothing Then
        MsgBox "No cells with background color were found.", vbInformation
        Exit Sub
    End If

    Set firstFound = foundCell

    Do
        ' Copy the found cell to the destination
        foundCell.Copy Destination:=pasteCell
        ' Move the destination one cell to the right
        Set pasteCell = pasteCell.Offset(0, 1)
        ' Find the next matching cell
        Set foundCell = searchArea.Find(What:="", After:=foundCell, SearchFormat:=True)
    Loop While Not foundCell Is Nothing And foundCell.Address <> firstFound.Address

End Sub

Key Points of the Code

1. Application.FindFormat

• Application.FindFormat.Clear: Resets any previous search formatting criteria.
• Application.FindFormat.Interior.Pattern = xlPatternSolid: Specifies that we are looking for cells that have a solid fill color.

2. Range.Find with SearchFormat

• searchArea.Find(..., SearchFormat:=True): This argument is crucial. It tells Excel to search based on the format defined in FindFormat, rather than just the cell value.

3. Copy Destination

• Copy Destination:=pasteCell: Copies the content and formatting of the found cell to the destination cell.

Important Notes

• Conditional Formatting: This method cannot detect colors applied via Conditional Formatting. It only finds manually applied cell colors.
• White/No Fill: Cells with no fill color are ignored.
• Destination Space: Since the code copies cells horizontally to the right, ensure there is enough empty space if you are processing a large amount of data.

Summary

Using VBA allows you to easily extract only the cells that have a background color set.

This contributes significantly to improving work efficiency by accurately detecting cells highlighted for attention or those that are difficult to check visually. You can adapt this method to paste results into a different sheet or categorize them by specific colors.