VBA & Macro Problems

How to Compare Excel Columns and Highlight Matching Words Using VBA

John Wilson Sep 27, 2026 869 views

Question details

The user needs to compare a column containing individual words against a column containing multiple words per cell, and format the matching text.

How to Compare Excel Columns and Highlight Matching Words with VBA

Product: Excel
Device & OS: not provided
Scenario: Cross-referencing a keyword list with a text phrase list to visually identify matching words within the longer text strings.
Observed behavior: The goal is to automatically identify matching words across ranges and apply text formatting, such as bold text or cell highlighting, using a VBA macro.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet software ribbon and remember to save your file as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.

Solution 1Recommended

Use a VBA Macro with InStr to Bold and Highlight Matches

Create a custom VBA script that loops through both column ranges and uses the InStr function to find and format matching text automatically.

This solution relies on nesting two loops: one to iterate through the list of single words, and another to search through the phrase column. By using the 'InStr' function with 'vbTextCompare', the script performs a case-insensitive search to locate exact matches within the longer strings.

Open the VBA Editor

Navigate to the Developer tab on the top ribbon and click 'Visual Basic' (or press ALT + F11 on your keyboard) to open the Microsoft Visual Basic for Applications window.

Insert a New Module

In the VBA Editor, go to the top menu and click 'Insert' > 'Module'. This will create a blank workspace where you can paste your macro code.

Write the Comparison Code

Set up your macro to define your two ranges (for instance, Column A for single words and Column B for phrases). Use a nested 'For Each' loop to iterate through the cells in both ranges.

Apply the InStr Function

Inside the loop, use 'InStr(1, phraseCell.Value, wordCell.Value, vbTextCompare)' to locate the starting character position of the matching word in the phrase cell.

Format the Matched Text

If a match is found (InStr > 0), use the '.Characters(start, length).Font.Bold = True' property to bold the specific matching word inside the phrase cell. You can also highlight the corresponding cell in your single-word list using '.Interior.ColorIndex = 6' (for yellow).

Run the Macro

Close the VBA editor to return to your spreadsheet. Press ALT + F8 to open the Macro dialog box, select your newly created macro, and click 'Run'.

Use a VBA Macro with InStr to Bold and Highlight Matches

Case Sensitivity Settings: The 'vbTextCompare' argument ensures the search ignores uppercase and lowercase differences. If you require a strict, case-sensitive match, change this parameter to 'vbBinaryCompare' in your script.

Advanced Spreadsheets Made Easy

Use WPS Spreadsheet to Run Macros and Compare Data

WPS Office provides robust native VBA support, allowing you to run complex macros for text comparison and formatting effortlessly. It is a highly compatible and lightweight solution for all your spreadsheet automation needs.

1. Enable Developer Tools: Launch WPS Spreadsheet, navigate to the 'Developer' tab on the main ribbon, and click 'VBA Editor' to open the coding environment.
2. Insert the Macro Code: Click 'Insert' > 'Module' within the VBA editor and paste your text comparison and highlighting script into the blank module.
3. Execute the Script: Click the 'Run' button on the toolbar or press F5 to execute the macro. The matching words will instantly be bolded and highlighted in your worksheet.

Fully supports VBA macros for complex data comparison and automation.Highly compatible with Microsoft Excel .xlsx and .xlsm file formats.Lightweight software with a fast, straightforward installation process.Familiar user interface that minimizes the learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro highlighting partial words instead of exact matches?

The standard 'InStr' function finds substrings anywhere within the text, meaning it will match 'car' inside 'carpet'. To prevent this, you can modify your code to check for space boundaries around the word or use Regular Expressions (RegEx) with word boundary tags (\b) for strict whole-word matching.

How do I save a spreadsheet file containing a VBA macro?

You must go to 'File' > 'Save As' and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown. If you save the file as a standard .xlsx workbook, all VBA code will be permanently discarded when you close the file.

Can I extract the matching words to a new column instead of just bolding them?

Yes. Instead of using the '.Characters' formatting method, you can adjust your VBA script to append the found words to a string variable and output that variable into an adjacent column, separating multiple matched words with a comma.

Does WPS Spreadsheet support running Excel VBA macros?

Yes, WPS Office fully supports VBA macros in its professional and business editions, as well as specific free regional versions. You can open .xlsm files and execute your existing Excel VBA scripts natively.

Excel & Data Problems