VBA & Macro Problems

How to Highlight Entire Rows if Column O is Blank using Excel VBA

Nimra Malik Oct 21, 2026 868 views

Question details

Use an Excel VBA macro to scan column O and automatically highlight the entire corresponding row when the cell is blank.

How to Highlight Entire Rows if Column O is Blank using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating spreadsheet formatting to easily identify and highlight records with missing data in a specific column.
Observed behavior: The macro applies a customized fill color to the entire row for every instance where the corresponding cell in column O contains no data.

Before you start

Ensure your Excel workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) format so you do not lose your VBA code when closing the file.

Solution 1Recommended

Use a VBA Macro to Scan and Highlight Blank Rows

This solution provides the exact VBA script needed to iterate through all used rows and apply a background color to any row where column O is empty.

This macro calculates the last used row in your active sheet and loops through each row. It checks the length of the cell contents in column O, highlighting the entire row if the length is zero.

Open the VBA Editor

With your Excel workbook open, press the shortcut keys Alt + F11 to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

In the top menu bar, click on 'Insert' and select 'Module' from the dropdown list. This will open a blank code window for your macro.

Paste the Macro Code

Copy and paste the following VBA code into the module window: Sub HighlightBlankRows() Dim lastRow As Long Dim i As Long lastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 For i = 1 To lastRow If Len(ActiveSheet.Cells(i, "O").Value) = 0 Then ActiveSheet.Rows(i).Interior.ColorIndex = 6 End If Next i End Sub

Run the Macro

Click anywhere inside the code block and press the F5 key on your keyboard, or click the 'Run' button (the green triangle) in the toolbar to execute the macro.

Use a VBA Macro to Scan and Highlight Blank Rows

Customizing the Highlight Color: The value 'ColorIndex = 6' applies a standard yellow fill. You can change this number in the code to apply different colors (e.g., 3 for red, 4 for green, 5 for blue).

Advanced Spreadsheet Formatting with WPS

Run Macros and Automate Tasks with WPS Spreadsheet

WPS Office provides excellent support for VBA macros, allowing you to seamlessly write, edit, and run scripts to highlight blank rows or format your data without any complex setup.

1. Open your Document in WPS: Launch WPS Spreadsheet and open your .xlsm data file.
2. Access the Developer Tools: Click on the 'Developer' tab in the top ribbon menu and select 'VBA Editor'.
3. Insert Module and Paste Code: Right-click on your workbook project in the editor, choose 'Insert' > 'Module', and paste the highlighting macro code.
4. Run the Code: Press F5 or click the Run button to instantly highlight the rows missing data in column O.

Fully compatible with Microsoft Excel Macro-Enabled (.xlsm) filesBuilt-in VBA editor for running existing Excel macros smoothlyLightweight application that uses minimal system resourcesFamiliar tabbed interface makes navigation intuitive

microsoft office alternative - wps office

Frequently Asked Questions

How do I apply this VBA macro to a different column instead of O?

To target a different column, locate the line `If Len(ActiveSheet.Cells(i, "O").Value) = 0 Then` in the code. Change the letter "O" to your desired column letter, such as "B" or "Z".

Will this macro highlight rows if the cell in column O contains a space?

No. The code checks if the character length is exactly zero (`Len(...) = 0`). If a cell contains a blank space, its length is at least 1, so the row will not be highlighted. You must manually clear spaces or wrap the value with the `Trim` function in your code to ignore them.

How do I remove the highlight colors applied by this macro?

To remove the color, select the affected rows (or the entire sheet), navigate to the 'Home' tab on your Excel ribbon, click the 'Fill Color' paint bucket icon, and choose 'No Fill'.

Can I highlight just the data range instead of the entire row?

Yes. Instead of using `ActiveSheet.Rows(i).Interior.ColorIndex = 6`, you can specify a range limits, for example, `ActiveSheet.Range("A" & i & ":Z" & i).Interior.ColorIndex = 6` to only highlight columns A through Z in that row.

Excel & Data Problems