VBA & Macro Problems

How to Perform an Exact Search and Highlight Cells in Green with Excel VBA

Emma Brown Oct 20, 2026 869 views

Question details

The user needs an Excel VBA macro that searches for an exact string and highlights the perfectly matching cell in green, specifically avoiding partial matches.

How to Perform an Exact Search and Highlight Cells in Green with Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Building a VBA search tool or macro to locate specific entries (like 'DOG123' instead of 'DOGS123' or 'G123') and apply background color formatting.
Observed behavior: The current search function defaults to finding partial matches or whichever value appears first, rather than strictly matching and highlighting only the exact typed value.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application and save your workbook as a Macro-Enabled Workbook (.xlsm) before adding or running any VBA scripts.

Solution 1Recommended

Use Range.Find with LookAt:=xlWhole for Exact Matches

Modify the VBA Find method parameters to enforce exact string matching, then apply a green interior color to the localized range.

By default, the Excel VBA Find method may remember previous search settings or default to partial matching (xlPart). Explicitly declaring LookAt:=xlWhole ensures that only cells containing the exact search string are identified.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor, then click Insert > Module to create a new script area.

Write the Exact Match Macro

Define your search function using Range.Find. Set the LookAt parameter to xlWhole. For example: Set foundCell = ws.Columns("B").Find(What:=searchText, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).

Apply Green Highlighting

Add logic to change the cell background color if a match is found. Use the RGB color code for green: foundCell.Interior.Color = RGB(0, 255, 0).

Use Range.Find with LookAt:=xlWhole for Exact Matches

Case Sensitivity: Set MatchCase:=True if you want 'DOG123' and 'dog123' to be treated as different entries, or keep it as MatchCase:=False to ignore letter casing.

Solution 2

Use a For Each Loop with Select Case

Iterate through all non-empty cells in a specific column to find exact matches for predefined values without using the Find method.

Automate with WPS

Run Macros and Highlight Exact Matches with WPS Spreadsheet

WPS Office provides excellent compatibility with Microsoft Excel VBA macros. You can seamlessly run your exact-match search scripts in WPS Spreadsheet to automate complex formatting tasks efficiently.

1. Open Your Workbook: Launch WPS Spreadsheet and open your Macro-Enabled Workbook (.xlsm).
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'VB Editor' to access the macro coding environment.
3. Insert the Search Script: Paste your exact match search macro into a module, ensuring LookAt:=xlWhole is included in your Find parameters.
4. Run the Macro: Click the Run button or assign the macro to a spreadsheet button to automatically search and highlight the matching cells in green.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm)Supports advanced macros and VBA scripts for task automationFamiliar ribbon interface makes it easy to navigate and insert codeLightweight and runs smoothly on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA search highlighting cells that only contain part of my search term?

By default, the Excel VBA Find method may retain previous search settings that used partial matching (LookAt:=xlPart). You must explicitly declare LookAt:=xlWhole in your VBA script to force an exact match.

How do I remove the green highlight before running a new search?

Before executing the search and highlight command, you can clear existing formatting by adding ws.Cells.Interior.ColorIndex = xlNone at the beginning of your macro to remove all background colors in the sheet.

Can I search for multiple exact names at the same time?

Yes, you can use a loop combined with an array of search terms or a Select Case statement to iterate through multiple specific values and highlight all matching cells in one execution.

What is the RGB code for a standard green highlight in VBA?

The standard bright green color in VBA is represented by the RGB function as RGB(0, 255, 0). You can assign this directly to the Interior.Color property of a cell range.

Excel & Data Problems