[Excel VBA] How to Automatically Color Cells Containing Specific Strings | Utilizing ReplaceFormat

When highlighting cells containing specific keywords in Excel, using Filters or Conditional Formatting is common. However, using VBA allows for the automation of batch coloring processes.

This article introduces a concise and powerful VBA code to color cells containing specific strings using the ReplaceFormat feature.

Useful Scenarios

• Color-coding cells containing names or keywords for instant recognition.
• Eliminating manual highlighting tasks to achieve process automation.
• Constructing a coloring logic controllable manually, separate from Conditional Formatting.

VBA Code Example

The following code automatically sets the background color to yellow (ColorIndex = 6) for all cells containing the string “Yamada” within the range B2:F11.

Sub HighlightCellsContainingKeyword()

    Dim keyword As String
    keyword = "Yamada"

    ' 1. Clear previous replacement formatting
    Application.ReplaceFormat.Clear
    
    ' 2. Set the highlight color (Yellow)
    Application.ReplaceFormat.Interior.ColorIndex = 6

    ' 3. Execute Replace with formatting
    ' Note: To preserve the text, set Replacement to the keyword itself.
    Range("B2:F11").Replace _
        What:=keyword, _
        Replacement:=keyword, _
        LookAt:=xlPart, _
        SearchFormat:=False, _
        ReplaceFormat:=True

End Sub

Supplementary Explanation of Syntax

Note on Replacement Argument

In the code above, Replacement:=keyword is used.

If Replacement:=”” is used, the keyword will be replaced by an empty string (deleted) while the formatting is applied. To apply formatting without changing the cell’s value, the Replacement argument must match the What argument (the keyword).

Important Notes

1. Partial Match: This method uses LookAt:=xlPart (partial match). To match the entire cell content only, use xlWhole.
2. Performance: If the search range is too large, processing may take time. It is recommended to limit the range to the necessary area.
3. Formulas: This searches displayed values but does not highlight parts of a formula string itself.

Summary

By utilizing VBA’s Replace method and ReplaceFormat, background colors can be applied in bulk to cells containing specific strings.

This is highly useful in scenarios requiring the visualization of specific keywords, such as business reports, lists, or inquiry data.

Applying this method allows for flexible operations, such as color-coding for multiple keywords or limiting processing to specific columns. Please utilize this for your automation needs.