Highlight Blank Cells

What it does

Excel's Conditional Formatting can highlight blanks, but setting it up takes four clicks and a dialog box, and the rule sticks around forever unless you remember to delete it. The macro is a one-shot version. Select your range, run it, every blank or whitespace-only cell turns light red. After you're done auditing, hit Ctrl+Z and the colors revert. No lingering conditional rule, no stale highlight that gets confusing six months later when someone else opens the workbook.

Real-world example

Picture a middle-school math teacher building the quarterly progress reports. She maintains a grade book with 28 students across 5 columns of assignments per quarter. Before she runs the macro that turns numeric scores into letter grades and prints the reports, she wants to make sure she hasn't forgotten to enter any score. Missing scores show up as a stealth zero in the gradebook average, which means a student who genuinely turned in 4 of 5 assignments looks like they got a 60 average instead of an 80.

Her current method: scan column by column, eyeball for empty cells, and try to remember which ones were excused absences (deliberately blank) versus genuinely missing entries. About 5 minutes per quarter per class, and she misses something once every two quarters because at 11pm on a Sunday the columns blur together.

With this macro she selects the grade block (28 rows by 5 columns), runs the tool, and every blank cell lights up bright pink. The popup tells her there are 7 blank cells. She walks through each one. Three are excused absences (and she leaves them blank, marks them with a yellow fill so she remembers). Four are scores she forgot to enter, which she fills in from her paper records. She hits Ctrl+Z to revert the pink highlighting on the still-blank excused cells before printing the report. Total elapsed: 3 minutes including the 30 seconds to enter the missing scores.

On a 28-student 5-assignment grade block, expect about 4 minutes saved per quarter and one fewer "why did my child get a 60" parent email per year. The bigger win is the discipline of reviewing every gap before grades go out, which the macro makes feasible.

How to use

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

1. Select the range you want to audit. This can be a single column, a block of cells, or a whole sheet via Ctrl+A.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick HighlightBlankCells, click Run.
3. The macro paints every blank and whitespace-only cell light red. The popup reports the count.
4. When you're done auditing, hit Ctrl/Cmd + Z to revert the colors all at once.

FAQ

What counts as blank?

A cell is treated as blank if it's truly empty, contains only an empty string "", or contains only whitespace characters (spaces, tabs). So a cell with a stray space gets highlighted alongside genuinely empty cells. This is on purpose — invisible whitespace is a frequent source of "this cell looks empty but VLOOKUP says it isn't" mysteries.

Will it overwrite existing cell colors?

Yes, in the cells it highlights. If you have existing color-coding (yellow flags, green confirmations) on a blank cell and the macro highlights that cell, the prior color is replaced with light red. Hitting Ctrl+Z immediately reverts everything in one step. If you need to preserve existing colors, work on a copy of the file or use Conditional Formatting instead.

Does it count cells that are blank because of a formula like =IF(A1=0,"",A1)?

Yes. A cell whose formula evaluates to "" is treated as blank by the macro and gets highlighted. The cell still contains a formula (and gets unhighlighted on Ctrl+Z), but the macro can't tell the difference between a formula-blank and a typed-blank, and that's actually what you usually want for auditing.

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(255, 199, 206) and the font is RGB(156, 0, 6). Change those numbers in the .bas file, re-import, and you're done. The values match the standard "Bad" cell style in Excel for visual consistency.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running reverts every painted cell to its prior color in one step. That's actually how this tool is designed to be used: highlight, audit, fix the gaps, undo. The macro runs entirely offline. Your data never leaves your machine.