[Excel VBA] How to Search Cells by Format (Fill Color) | Extracting Colored Cells

When you want to find “cells filled in red” or “cells with a specific font” in Excel, doing it manually can take a lot of time.

This article explains how to use VBA to search for cells with a specific format and extract their content to another location.

Expected Use Cases

• You want to list error cells (painted red) in a separate table.
• You want to extract only the cells highlighted by conditional formatting.
• You want to automate data collection or analysis based on formatting.

Example of VBA Code

The following code searches for cells with “Fill Color Red (ColorIndex = 3)” in the range B2:E11. It then outputs the values vertically starting from cell H2.

Sub FindCellsByFormat()

    Dim searchRange As Range
    Dim firstHit As Range
    Dim hitCell As Range
    Dim outputCell As Range

    ' 検索対象範囲
    Set searchRange = ActiveSheet.Range("B2:E11")

    ' 出力開始セル
    Set outputCell = ActiveSheet.Range("H2")

    ' 検索書式の初期化と設定
    Application.FindFormat.Clear
    Application.FindFormat.Interior.ColorIndex = 3 ' 赤く塗られたセルを検索

    ' 書式一致セルの検索
    Set hitCell = searchRange.Find(What:="", SearchFormat:=True)

    If hitCell Is Nothing Then
        MsgBox "該当する書式のセルは見つかりませんでした。", vbInformation
        Exit Sub
    End If

    Set firstHit = hitCell

    Do
        outputCell.Value = hitCell.Value
        Set outputCell = outputCell.Offset(1, 0)
        Set hitCell = searchRange.Find(What:="", After:=hitCell, SearchFormat:=True)
    Loop While Not hitCell Is Nothing And hitCell.Address <> firstHit.Address

End Sub

Key Points Explanation

Explanation of the Process

• Application.FindFormat.Clear Initializes the format settings for the search condition.
• Application.FindFormat.Interior.ColorIndex = 3 Sets the color number 3 (Red) as the target for the search.
• SearchFormat:=True Specifies that the format is the search criteria. Without this, the code will not work as intended.
• Do...Loop Retrieves all matching cells using a loop if there are multiple matches.

Notes and Supplements

1. ColorIndex: This is Excel’s internal color number. The displayed color might look slightly different depending on your environment.
2. What:="": This specification is mandatory. When searching by format, the search string is ignored, but you must still include this argument.
3. Cell Types: Formula cells or blank cells will also be found if they match the specified format.

Summary

By combining the VBA Find method with SearchFormat:=True, you can search based on format rather than cell content.

This technique is very useful when you want to automate extracting colored cells or formatting data. If you want to reduce visual checks, prevent errors, and collect data efficiently, please try this method.