VBA & Macro Problems

How to Select the Active Row and Column in Excel with VBA

Huma Ashraf Ch Oct 15, 2026 869 views

Question details

The user needs a VBA macro to simultaneously select and highlight the entire row and column that intersect at the currently active cell.

How to Select the Active Row and Column in Excel with VBA

Product: Excel
Device & OS: not provided
Scenario: Creating a crosshair highlighting effect in a spreadsheet to easily track data along the active row and column without manually selecting them.
Observed behavior: The user wants to automatically or manually trigger the selection of both the active row and column intersecting at a specific active cell.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that macros are allowed in your Trust Center settings before inserting and running VBA code.

Solution 1Recommended

Use Worksheet SelectionChange Event (Automatic Highlighting)

This method automatically selects the entire row and column intersecting at your active cell every time you click a new cell.

By using the Worksheet_SelectionChange event, the macro triggers automatically upon any cell selection change. This creates a dynamic crosshair effect, making it much easier to read large tables and trace data.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.

Access the specific worksheet module

In the Project Explorer pane on the left, double-click the specific worksheet (e.g., Sheet1) where you want this highlighting feature to function.

Insert the event code

Copy and paste this code into the module window: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.CountLarge > 1 Then Exit Sub Application.ScreenUpdating = False Dim columnNumber As Long, rowNumber As Long columnNumber = Target.Column rowNumber = Target.Row Dim columnLetter As String columnLetter = Split(Target.Parent.Cells(1, columnNumber).Address, "$")(1) Target.Parent.Range(columnLetter & ":" & columnLetter & "," & rowNumber & ":" & rowNumber).Select Application.ScreenUpdating = True End Sub

Test the automatic selection

Close the VBA Editor and return to your spreadsheet. Click on any single cell, and you will see the entire intersecting row and column selected automatically.

Use Worksheet SelectionChange Event (Automatic Highlighting)

Performance Tip: The Application.ScreenUpdating commands in the code prevent screen flickering while the macro rapidly updates the selection behind the scenes.

Solution 2

Create a Manual Macro for Active Row and Column Selection

If you only need to highlight the row and column occasionally, you can run a standard macro on demand rather than automatically.

Advanced Spreadsheet Features

Run VBA Macros and Manage Data with WPS Spreadsheet

WPS Office fully supports VBA macros, allowing you to seamlessly run your custom VBA scripts to highlight active rows and columns. Enjoy a lightweight and highly compatible spreadsheet experience.

1. Download and install: Download and install WPS Office Free on your computer from the official website.
2. Open your macro-enabled file: Open your .xlsm or .xls file using the WPS Spreadsheet application.
3. Access the Developer tools: Navigate to the Developer tab to access the built-in VBA Editor and manage your macros.
4. Run your VBA scripts: Insert and execute your custom VBA scripts to highlight active rows and columns just as you would in Microsoft Excel.

Fully compatible with Microsoft Excel (.xlsx, .xls, .xlsm) macro-enabled formats.Built-in VBA/Macro support for automating repetitive spreadsheet tasks.Lightweight design ensures fast loading and smooth performance on all devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Worksheet_SelectionChange event triggering?

Ensure that Macros are enabled in your Trust Center settings. Additionally, verify that you pasted the code into a specific Worksheet module (e.g., Sheet1) in the VBA Editor, rather than a standard Module, as worksheet events only trigger when placed in the corresponding sheet's code window.

Will selecting the entire row and column delete my current cell data?

No, selecting or highlighting a row and column only changes the interface focus on your screen. It does not alter, format, or delete any actual data within those cells.

How do I turn off the automatic row and column highlighting?

To stop the automatic selection, press Alt + F11 to open the VBA Editor, navigate to the worksheet module containing the code, and either delete the Worksheet_SelectionChange macro entirely or add an apostrophe (') before each line to comment it out.

Can I apply this VBA macro to all worksheets in my workbook?

Yes, to apply this selection behavior globally across your entire file, place the selection code into the `Workbook_SheetSelectionChange` event inside the `ThisWorkbook` module in the VBA Editor, rather than within an individual worksheet module.

Excel & Data Problems