VBA & Macro Problems

How to Highlight the Active Row in Excel with VBA

Huma Ashraf Ch Sep 29, 2026 868 views

Question details

The user wants to automatically highlight the entire row of the currently selected cell in an Excel worksheet.

How to Highlight the Active Row in Excel with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Improving data readability by tracking the active row as the user navigates through a large dataset.
Observed behavior: Currently, only the individual active cell has a border, making it difficult to read across long rows of data without losing track of the row.

Before you start

Before adding VBA code, ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to prevent losing your macro scripts when closing the file.

Solution 1Recommended

Use VBA Macro to Automatically Highlight the Active Row

Add a simple Worksheet SelectionChange event macro to automatically color the row whenever you select a new cell.

Open the Visual Basic Editor

Right-click the specific worksheet tab (e.g., 'Sheet1') at the bottom of your Excel window and select 'View Code' from the context menu.

Paste the VBA Script

In the code window that appears, paste the following code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False Cells.Interior.ColorIndex = xlNone Target.EntireRow.Interior.ColorIndex = 8 Application.ScreenUpdating = True End Sub

Save as Macro-Enabled

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown menu to ensure your code is preserved.

Test the Highlight

Return to your worksheet and click on any cell. The entire row containing the active cell will now be highlighted in cyan.

Use VBA Macro to Automatically Highlight the Active Row

Customizing the Highlight Color: You can change the 'ColorIndex = 8' value in the script to another number like 6 (yellow) or 3 (red) to suit your visual preference.

Solution 2

Use the Focus Cell Feature (Excel 365)

If you are using a newer version of Microsoft 365, you can use the built-in Focus Cell feature to highlight the active row and column without writing code.

Efficient Spreadsheet Management

Use Macros Seamlessly in WPS Spreadsheet

WPS Office offers full support for VBA macros in its Spreadsheet application, allowing you to easily automate tasks like highlighting active rows with exactly the same VBA code used in Microsoft Excel.

1. Open Your Spreadsheet in WPS Office: Download and launch WPS Office, then open the spreadsheet file you want to edit.
2. Access the Developer Tools: Navigate to the 'Tools' tab on the top ribbon and click on 'Developer' to reveal macro options.
3. Add the VBA Code: Click 'Visual Basic Editor', select your target sheet on the left panel, paste the highlighting macro, and save your file.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro formats.Built-in Visual Basic Editor for writing and running scripts easily.Lightweight, fast, and free to download for everyday office tasks.Familiar ribbon interface ensures zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Does this VBA code remove my existing cell background colors?

Yes, the line 'Cells.Interior.ColorIndex = xlNone' clears all background colors in the worksheet before applying the highlight to the new active row. If your worksheet relies on existing color formatting, this specific macro will erase them.

Why doesn't the macro work when I reopen my Excel file?

You may have saved the file as a standard Excel Workbook (.xlsx) instead of a Macro-Enabled Workbook (.xlsm), which deletes the code. Alternatively, your Trust Center settings might be blocking macros from running. Make sure to click 'Enable Content' if prompted at the top of the screen.

Where exactly do I enter the VBA code for a specific sheet?

You enter it directly into the code module for that specific worksheet. Right-click the sheet tab at the bottom (for example, 'Sheet1'), select 'View Code', and paste it in the large blank window that appears on the right side of the Visual Basic Editor.

Excel & Data Problems