Excel VBA: Color Matching Text from Column B in Column A
Question details
The user needs to find each nonblank text value in column B within the text in column A and format every matching substring with a different font color.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A worksheet contains source text in column A and search terms in column B. The user wants a macro to locate all occurrences, including repeated matches, and color them automatically.
- Observed behavior
- Matching text is not yet highlighted or color-formatted in column A.
Save the workbook as a macro-enabled Excel file (.xlsm), and confirm that the search terms are in column B while the text to scan is in column A. Remove blank rows or adjust the range logic if your data starts below row 1.
Use a VBA macro to color every matching substring in column A
Use this solution when column B contains the terms to find and column A contains the text that should be partially formatted.
The macro checks each nonblank cell in column A against every nonblank cell in column B, uses a case-insensitive search, and formats every occurrence rather than only the first match. It resets existing font colors before processing and temporarily disables screen updates to improve performance.
In Excel, open the target workbook, press Alt+F11, select Insert > Module, and create a standard module.
Paste this code into the module: Sub ColorMatchingText() Dim ws As Worksheet Dim lastRowA As Long, lastRowB As Long Dim cellA As Range, cellB As Range Dim sourceText As String, searchText As String Dim position As Long, textLength As Long Set ws = ActiveSheet Application.ScreenUpdating = False lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ws.Range("A1:A" & lastRowA).Font.ColorIndex = xlColorIndexAutomatic For Each cellA In ws.Range("A1:A" & lastRowA) sourceText = CStr(cellA.Value) If Len(sourceText) > 0 Then For Each cellB In ws.Range("B1:B" & lastRowB) searchText = CStr(cellB.Value) If Len(searchText) > 0 Then textLength = Len(searchText) position = 0 Do position = InStr(position + 1, sourceText, searchText, vbTextCompare) If position = 0 Then Exit Do cellA.Characters(Start:=position, Length:=textLength).Font.Color = vbRed Loop End If Next cellB End If Next cellA Application.ScreenUpdating = True End Sub
Return to Excel with Alt+F11, place the cursor inside ColorMatchingText, press F5, or choose Run > Run Sub/UserForm. If prompted, enable macros for the workbook.
Check column A for red matching text. To use another color, replace vbRed with a color constant or an RGB value, such as RGB(0, 102, 204), in the Characters.Font.Color assignment.
Adjust the macro for custom ranges or matching rules
Use this option when your headers occupy row 1, your data begins on another row, or matching must be case-sensitive.
Run a text-matching macro in WPS Spreadsheets
WPS Spreadsheets can perform the same column-based text matching workflow with a VBA macro in editions that support macros. Open the macro editor, add the matching procedure, run it on the active sheet, and save the workbook in a macro-enabled format when needed.
- 1. Open the macro editor: In WPS Spreadsheets, open the workbook and use the Tools or Developer menu to open the VBA or macro editor, depending on your WPS version.
- 2. Insert the procedure: Create a standard module and paste the ColorMatchingText procedure, keeping the source range in column A and the search range in column B.
- 3. Run the macro: Return to the spreadsheet, choose Tools > Macro > Macros, select ColorMatchingText, and click Run.
- 4. Save the workbook: Use Save As and select .xlsm when you need to preserve the macro and continue editing the file in Excel or another macro-enabled spreadsheet application.

Frequently Asked Questions
How can I color all occurrences instead of only the first match?
Use an InStr loop that continues searching from position + 1 until InStr returns 0. The provided ColorMatchingText macro uses this loop and formats every occurrence in each column A cell.
Does the macro distinguish uppercase and lowercase letters?
No. The macro uses vbTextCompare, so values such as apple and Apple match. Replace vbTextCompare with vbBinaryCompare for case-sensitive matching.
How do I skip header rows in columns A and B?
Change the starting ranges from A1 and B1 to A2 and B2. For example, use ws.Range("A2:A" & lastRowA) and ws.Range("B2:B" & lastRowB).
Why does the macro remove existing font colors in column A?
The reset line ws.Range("A1:A" & lastRowA).Font.ColorIndex = xlColorIndexAutomatic clears previous highlighting so the new results are consistent. Delete or modify that line if existing colors must be preserved.
What file format preserves the VBA macro?
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). A standard .xlsx file can store the worksheet data but does not preserve VBA project code.




