VBA & Macro Problems

How to Search All Excel Worksheets and Highlight Matching Rows with VBA

Bushra Parveen Oct 22, 2026 869 views

Question details

The user needs an Excel VBA macro to search for a specific keyword across all worksheets in a workbook and automatically highlight the matching cells or their entire rows in yellow. They may also need to limit the search to selected columns.

How to Search All Excel Worksheets and Highlight Matching Rows with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Navigating a large workbook with multiple sheets to manually locate and highlight specific data points is tedious. The user wants to automate this process to quickly identify matching records for easier data tracking.
Observed behavior: Currently, there is no built-in single-click feature in Excel to highlight all cross-sheet search results automatically. The process must be accomplished using a custom VBA macro.

Before you start

Before running the macro, ensure you have saved a backup copy of your workbook. VBA macro actions, such as applying interior fill colors across multiple sheets, usually clear the Undo history and cannot be easily reverted.

Solution 1Recommended

Use a VBA Macro to Search All Sheets and Highlight Rows

Implement a VBA script using the Find and FindNext methods to loop through all worksheets and apply a yellow highlight to the rows containing the specific keyword.

This solution utilizes a standard VBA loop across all worksheets. It groups all matching cells into a single range using the Union method and then applies the formatting to the entire row using an Intersect with the used range to prevent formatting empty cells endlessly to the right.

Open the VBA Editor

Open your Excel workbook and press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, click on Insert in the top menu bar, and then select Module to create a blank window for your code.

Paste the Macro Code

Copy and paste the following code into the module window: ```vba Sub FindValues() Dim C As Range, D As Range, sh As Worksheet Dim myFindString As String, firstAddress As String myFindString = Application.InputBox("What do you want to find?") For Each sh In ActiveWorkbook.Worksheets With sh.UsedRange.Cells Set D = Nothing firstAddress = "" Set C = .Find(myFindString, LookIn:=xlValues, LookAt:=xlWhole) If Not C Is Nothing Then Set D = C firstAddress = C.Address Set C = .FindNext(C) If C.Address <> firstAddress Then Do Set D = Union(D, C) Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address <> firstAddress End If End If If Not D Is Nothing Then With Intersect(D.EntireRow, sh.UsedRange) .Interior.ColorIndex = 6 End With End If End With Next sh End Sub ```

Run the Macro

Press F5 or click the Run button. A prompt will appear asking what you want to find. Enter your keyword and click OK. All matching rows across all sheets will be highlighted in yellow.

Use a VBA Macro to Search All Sheets and Highlight Rows

Color Customization: The line '.Interior.ColorIndex = 6' sets the color to yellow. You can change the number to 3 for red, 4 for green, or 5 for blue depending on your preference.

Solution 2

Modify Code to Search Specific Columns and Highlight Only Cells

Adjust the VBA script to limit the search scope to specific columns (like B, C, and F) and highlight only the exact matching cell instead of the entire row.

Automate Tasks with WPS Spreadsheet

Use Macros to Search and Highlight in WPS Office

WPS Office Spreadsheet fully supports VBA macros, allowing you to use the exact same scripts to automate complex searches. You can instantly search across all worksheets and highlight matching rows or cells effortlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook you want to search.
2. Access the Macros Tool: Navigate to the Tools tab on the top ribbon and click on Macros > Macros... (or press Alt + F8 on your keyboard).
3. Insert the VBA Code: Click Create to open the WPS VBA Editor. Right-click your VBAProject, select Insert > Module, and paste the FindValues code.
4. Execute the Search: Run the macro directly from the editor to prompt the search box and highlight your matching data across all tabs.

Fully compatible with Microsoft Excel macro formats (.xlsm, .xls).Built-in VBA editor for seamless code execution and data automation.Lightweight and optimized for fast performance on large datasets.Completely free to use with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Will this VBA macro search for partial matches or exact matches?

The provided macro searches for exact matches because it uses the parameter `LookAt:=xlWhole`. If you want the macro to highlight rows that contain a partial match (e.g., finding 'apple' within 'green apple'), change that parameter to `LookAt:=xlPart`.

How do I remove the highlights added by the macro?

To remove the highlights, select all the worksheets by right-clicking a sheet tab and choosing 'Select All Sheets'. Then, click the 'Fill Color' dropdown icon in the Home tab and select 'No Fill'. Alternatively, you can write a short macro that applies `.Interior.ColorIndex = xlNone` to the `UsedRange` of every worksheet.

Why do I get a 'Type Mismatch' or 'Object Required' error when limiting the search to specific columns?

This error typically occurs if the columns you specified (like B:C) fall entirely outside the `UsedRange` of a particular worksheet, making the `Intersect` result invalid. To fix this, you can add basic error handling like `On Error Resume Next` right before the `With Intersect(...)` line, and `On Error GoTo 0` after the `End With`.

Excel & Data Problems