Automatically Highlight Active Row and Column in Excel when Click on any Cell

Highlighting the active row and column in Excel can make it much easier to visualize data and stay focused while working on large spreadsheets. This feature allows you to automatically highlight the row and column of the currently selected cell, helping you track your position in the spreadsheet effortlessly.
Below, we’ll walk you through how to implement this functionality step by step.

1. Using Conditional Formatting to Highlight the Active Row and Column

You can achieve this effect using Conditional Formatting with simple formulas:

Steps:

1. Select the entire worksheet or a specific range where you want the highlighting to apply.
2. Go to the Home tab, click Conditional Formatting, and choose New Rule.
3. In the dialog box, select Use a formula to determine which cells to format.
4. Enter the following formulas to highlight the active row and column:
   • • For highlighting the active row:

   =ROW()=CELL("row")

   • • For highlighting the active column:

   =COLUMN()=CELL("col")

5. Set a format (e.g., a fill color) for the highlighted cells.
6. Click OK to apply the rule.

Now, whenever you click on a cell, the corresponding row and column will be highlight but you have to Press F9 button each time. to automate the process follow the below Step of VBA coding.

2. Using VBA for Dynamic Highlighting

If you prefer a more dynamic approach where the highlight updates automatically without needing conditional formatting, you can use a simple VBA script.

Steps:

1. 1. Press Alt + F11 to open the VBA Editor.
   2. In the left pane, double-click on the sheet where you want this functionality (e.g., Sheet1).
   3. Copy and paste the following VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = True
End Sub

1. Close the VBA Editor and return to Excel.
2. Save your file as a Macro-Enabled Workbook (.xlsm).

Explanation:
This simple VBA script ensures that Excel continuously refreshes the screen (ScreenUpdating = True) whenever a cell selection changes. This triggers any visual updates, such as conditional formatting rules for highlighting the active row and column.

Video Tutorial

Download Code File

Join Our Telegram Group Join Our WhatsApp Group

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

Share via:

WhatsApp

Facebook

Twitter

LinkedIn

Reddit

Email