Highlight Rows Containing Keyword

What it does

Type a keyword, and the macro highlights every row in your selection where at least one cell contains that word or phrase. The search is case-insensitive and matches partial strings, so "acme" catches "Acme Corp" and "ACME LLC" both. Excel's built-in Ctrl+F finds cells, but it doesn't color whole rows. Conditional formatting can do this too, but it leaves a live formula rule embedded in the file that recalculates forever and travels with the sheet when you share it. This macro paints cells statically, no rule attached.

Real-world example

A finance analyst at a mid-sized logistics company in Pune is reviewing a monthly payables export from their ERP. The sheet has 480 rows covering dozens of vendors. The CFO has flagged one vendor for review before the payment run and wants the analyst to pull out every line item tied to that vendor before the end of day.

The obvious move is Ctrl+F, but that highlights one cell at a time and doesn't color the whole row. The analyst could set up a conditional formatting rule with a SEARCH formula, but it means clicking through four dialog boxes, building the formula correctly, and then remembering to clear the rule before forwarding the file. On a deadline, that's the kind of friction that causes mistakes.

Instead, she selects columns A through G (the full row of data), opens the macro with Alt+F8, picks HighlightRowsContainingKeyword, types the vendor name, and clicks Run. In about three seconds, 14 rows are painted yellow. She can see them at a glance, filter by color if she wants, or just scroll through the highlighted rows.

On a 480-row payables sheet, finding and visually confirming every row for a specific vendor by hand takes 10 to 15 minutes. The macro does it in under five seconds. The bigger payoff: she can repeat this for three more vendor names the CFO mentioned, clearing each set of highlights with Home > Clear > Clear Formats between passes. No rule to manage, no formula to explain to a colleague.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range you want to search. Include all columns you care about, because the macro scans every cell in the selection.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick HighlightRowsContainingKeyword, click Run.
3. Type your keyword in the prompt. The search is case-insensitive and matches partial text.
4. Click OK. Matching rows are highlighted in yellow.
5. To clear the highlights: select the range and use Home > Clear > Clear Formats.

FAQ

Is the search case-sensitive?

No. The macro uses a case-insensitive comparison, so "acme", "Acme", and "ACME" all match. If you need a case-sensitive search, that requires a minor change to the VBA source: swap vbTextCompare for vbBinaryCompare on the InStr line.

Does it search inside formulas or just the displayed value?

It searches the cell's value, not the formula text. If a cell contains =VLOOKUP(...) that returns "Acme Corp", searching for "acme" will find it. If your formula text itself contains the keyword but the result doesn't, the macro won't flag that row.

Will it overwrite my existing cell colors?

Yes, for matching rows. If a cell in a matching row already has a background color, the macro replaces it with yellow. Non-matching rows are not touched at all. If you want to preserve existing colors, run on a copy of the sheet first. The macro works fully offline, so your data never leaves your machine.

Can I undo it?

Yes, Ctrl + Z (Windows) or Cmd + Z (Mac) immediately after running will undo the highlighting. To clear later without undo: select the range and choose Home > Clear > Clear Formats.

What if I want to highlight rows that match multiple keywords?

Run the macro once per keyword, using a different highlight color each time. Before the second run, change the fill color in the VBA source on the RGB(255, 235, 156) line: for example, RGB(180, 220, 255) gives a light blue. Alternatively, run the macro, filter by the yellow color to confirm the results, clear, then run again for the next keyword.

Does it work on data that spans multiple sheets?

No. The macro works only on the current selection, which must be on one sheet. To search across sheets, run it separately on each sheet.