VBA & Macro Problems

Excel VBA: Color Matching Text from Column B in Column A

Bushra ParveenBushra Parveen Nov 12, 2026 869 views

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.

Excel VBA: Color Matching Text from Column B in Column A
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the target workbook, press Alt+F11, select Insert > Module, and create a standard module.

2
Paste the macro

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

3
Run the macro

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.

4
Review and adjust the result

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.

Case-insensitive matching enabled: The vbTextCompare argument treats uppercase and lowercase letters as equivalent. The loop also colors repeated occurrences of the same search term within a cell.

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. 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. 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. 3. Run the macro: Return to the spreadsheet, choose Tools > Macro > Macros, select ColorMatchingText, and click Run.
  4. 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.
Automatically colors every matching substring found in column A using terms from column BSupports repeated matches and case-insensitive searches with VBA string functionsProvides a familiar spreadsheet interface with a lightweight workflow for reviewing resultsOpens and saves Microsoft Excel .xlsx and macro-enabled .xlsm files for easier migration
microsoft office alternative - wps office

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.