Highlight Cells With Comments

What it does

Cells with comments (the legacy yellow-triangle kind, the most common form in Windows and Mac Excel) carry context the cell value alone doesn't show. When you inherit a model, finding all the comments is tedious because the visual indicator (a tiny red triangle) blends in. The macro paints every cell with a comment a light green. Run it, scan visually for green, hover each cell to read the note, decide what to do. Then Ctrl+Z to revert the colors.

Real-world example

Picture a senior associate at a corporate development team about to lead her first integration model review. Her predecessor built the model two years ago and left detailed comments on the assumption cells: which source the number came from, which exec signed off, which date the assumption was last refreshed. The comments are the breadcrumb trail back to "is this number still valid". The model has about 4,200 cells of inputs and intermediate calcs. She doesn't know which 30 or 50 are commented.

Her current method: scroll the model looking for the tiny red triangle in the cell corners. The triangle is roughly 5 pixels wide and easy to miss when you're scanning at a glance. She finds the obvious ones (the big assumption block at the top), misses the subtle ones (a foreign-exchange override deep in the cost-of-capital tab), and learns about the missed ones months later when the FX rate changes and nobody knows the override is hardcoded.

With this macro she selects the entire used range of each tab, runs the tool, and every commented cell turns light green. The summary tab has 12 green cells; the FX tab has 4; the assumption block has 38. She walks through each one, reads the comment, decides whether the assumption is still current. Total elapsed for the audit pass: 25 minutes versus the 2 hours she'd spend scrolling and squinting otherwise.

On a 4,200-cell multi-tab model audit, expect about 90 minutes saved on the find-the-comments step. The bigger win is finding all the comments, including the subtle ones that get missed in the visual scan.

How to use

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

1. Select the range you want to scan. Ctrl+A inside the data block works for an entire sheet.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick HighlightCellsWithComments, click Run.
3. The macro paints every cell that has a comment light green. The popup reports the count.
4. When you're done auditing, hit Ctrl/Cmd + Z to revert the colors all at once.

FAQ

Does it find threaded comments (the Microsoft 365 conversation kind)?

No, only legacy Notes (the yellow-triangle kind). Threaded comments use a different VBA property (CommentThreaded) that isn't reliably available across all Excel versions, especially on Mac. For a typical inherited workbook the comments are almost always Notes anyway. If you specifically need threaded-comment detection, edit the .bas source to add a cell.CommentThreaded check inside an On Error Resume Next guard.

Will it overwrite existing cell colors?

Yes, on cells that have comments. If those cells already had a color (red flags, green confirmations), the macro replaces it with light green. Ctrl+Z immediately reverts everything. To preserve existing colors, work on a copy or use Excel's Find & Replace > Format > Comments dialog instead (slower, but doesn't paint).

Does it modify the comments themselves?

No. The macro only changes the cell's interior color and font color. The comment text, author, and position are untouched. So the visual indicator (red triangle) still works alongside the new green fill until you Ctrl+Z to revert.

Can I customize the highlight color?

Not from the macro UI, but the source code is plain VBA in the repo. The fill is RGB(198, 224, 180) (light green) and the font is RGB(40, 80, 30) (dark green). Change the numbers in the .bas file, re-import the macro, done.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running reverts every painted cell to its prior color in one step. The macro runs entirely offline. Your data never leaves your machine.