VBA & Macro Problems

How to Validate Required Cells and Highlight Empty Cells in Excel VBA

Algirdas Jasaitis Oct 10, 2026 868 views

Question details

The user needs an Excel VBA macro to validate a user-selected range, identify required cells that have been left empty, and highlight them.

How to Validate Required Cells and Highlight Empty Cells in Excel VBA

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Performing data validation on form entries where specific required cells must not be left blank before submission.
Observed behavior: The intended outcome is to clear any previous highlights, prompt the user to select a range via an InputBox, check for empty cells, highlight the blank ones in yellow, and display a completion message.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet program and have saved your workbook as a Macro-Enabled Workbook (.xlsm) to allow VBA execution.

Solution 1Recommended

Create a VBA Macro to Validate and Highlight Empty Cells

Use an InputBox to dynamically define the required range and loop through it to identify and highlight blank cells.

This solution utilizes the Application.InputBox method with Type:=8, which specifically allows users to select a range object using their mouse. By integrating this with a loop, you can quickly analyze large datasets for missing values.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor. Go to Insert > Module to create a new module for your code.

Set Up the InputBox Prompt

Write your subroutine and declare a Range variable. Use `Set rng = Application.InputBox("Select the required cells:", Type:=8)` to prompt the user to highlight the required area.

Clear Previous Formatting

Add `rng.Interior.ColorIndex = xlNone` right after your range selection to remove any existing background colors from a previous validation check.

Loop and Highlight Blank Cells

Create a loop using `For Each cell In rng`. Inside the loop, add an IF statement: `If IsEmpty(cell) Then cell.Interior.Color = vbYellow` to flag the empty cells.

Display a Summary Message

Conclude the macro with a `MsgBox` that alerts the user whether all required cells are filled or if some remain blank based on a counter variable tracking the empty cells.

Create a VBA Macro to Validate and Highlight Empty Cells

Handling Cancel Button Errors: When the user clicks 'Cancel' on an InputBox with Type:=8, it returns an error. Always add 'On Error Resume Next' before the InputBox line and check if the range 'Is Nothing' to prevent the macro from crashing.

Advanced Data Validation with WPS

Use WPS Spreadsheet to Run Your VBA Macros Seamlessly

WPS Office provides robust support for VBA macros, allowing you to validate data, highlight empty cells, and automate your repetitive workflows just like in Microsoft Excel.

1. Enable Macros in WPS: Open WPS Spreadsheet and navigate to the Developer tab to access macro settings.
2. Access the VBA Editor: Click on 'VBA Editor' or press Alt + F11 to open the development environment.
3. Paste and Run Your Code: Insert a new Module, paste your empty cell highlighting code, and click Run to execute the validation directly in your WPS worksheet.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro formats.Built-in VBA editor for advanced data validation and automation.Lightweight, fast, and free for fundamental office tasks.Familiar user interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle multiple non-contiguous ranges in the InputBox?

The InputBox with Type:=8 natively allows selecting multiple non-adjacent areas by holding the Ctrl key while selecting ranges with your mouse. The 'For Each cell In rng' loop in your macro will automatically process all selected areas seamlessly.

Can I change the highlight color from yellow to something else?

Yes, you can modify the 'vbYellow' constant in your VBA code to other standard VBA colors like 'vbRed', 'vbGreen', or use the RGB function like 'RGB(255, 0, 0)' for specific custom colors.

Does IsEmpty work if the cell contains a formula returning an empty string?

No, the IsEmpty function only evaluates to True for truly blank cells that contain no data or formulas. If you want to highlight formulas that return empty strings (""), change your condition to 'If cell.Value = "" Then'.

Why does my macro crash when I click Cancel on the InputBox?

Clicking Cancel causes the InputBox to return False instead of a valid Range object, throwing an Object Required error. Prevent this by placing 'On Error Resume Next' before the InputBox prompt, then verifying the range was set using 'If Not rng Is Nothing Then'.

Excel & Data Problems