And I got one last week too.

So I decided to create a tutorial and a video on it. It will save me some time and help the readers too. Below is a video where I show how to highlight the active row and column in Excel.

In case you prefer written instructions, below is a tutorial with exact steps on how to do it. Let me first show you what we are trying to achieve.

In the above example, as soon as you select a cell, you can see that the row and column also get highlighted. This can be helpful when you’re working with a large dataset and can also be used in Excel Dashboards. Now let’s see how to create this functionality in Excel. Download the Example File

Highlight the Active Row and Column in Excel

Here are the steps to highlight the active row and column on selection:

Select the data set in which you to highlight the active row/column. Go to the Home tab. Click on Conditional Formatting and then click on New Rule. In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format”. In the Rule Description field, enter the formula: =OR(CELL(“col”)=COLUMN(),CELL(“row”)=ROW()) Click on the Format button and specify the formatting (the color in which you want the row/column highlighted). Click OK.

The above steps have taken care of highlighting the active row and active column (with the same color) whenever there is a selection change event. However, to make this work, you need to place a simple VBA code in the backend. Here is the VBA code that you can copy and paste (exact steps also listed below): The above VBA code is run whenever there is a selection change in the worksheet. It forces the workbook to recalculate, which then forces the conditional formatting to highlight the active row and the active column. Normally (without any VBA code) a worksheet refreshes only when there is a change in it (such as data entry or edit). Also, an IF statement is used in the code to check if the user is trying to copy paste any data in the sheet. During copy paste, the application is not refreshed and it is allowed. Here are the steps to copy this VBA code in the backend:

Go to the Developer tab (can’t find the developer tab? – read this). Click on Visual Basic. In the VB Editor, on the left, you will see the project explorer that lists all the open workbooks and the worksheets in it. If you can’t see it, use the keyboard shortcut Control + R. With your workbook, double-click on the sheet name in which you have the data. In this example, the data is in Sheet 1 and Sheet 2. In the code window, copy and paste the above VBA code. You’ll have to copy and paste the code for both sheets if you want this functionality in both sheets. Close the VB Editor.

Since the workbook has VBA code in it, save it with a .XLSM extension. Download the Example File. Note that in the steps listed above, the active row and column would get highlighted with the same color. If you want to highlight the active row and column in different colors, use the below formulas:

=COLUMN()=CELL(“col”) =CELL(“row”)=ROW()

In the download file provided with this tutorial, I have created two tabs, one each for single color and dual color highlighting. Since these are two different formulas, you can specify two different colors.

Useful Notes:

Want to Level-up your Excel Skills? Consider joining one of my Excel courses:

Excel Dashboard Course Excel VBA Course

How to Highlight Blank Cells in Excel. Highlight Rows Based on a Cell Value in Excel. Highlight EVERY Other ROW in Excel. How to Move Rows and Columns in Excel. Creating Heatmap in Excel. How to Count Colored Cells in Excel. 24 Useful Macro Examples. Using Loops in Excel VBA.

Thank you in advance! Can you post how to work in timing Ex : 1,1.30,1.0,1.30 ,Sum=5 hrs If we call calculate in excel it comes =4.60 hrs Pls support can you help me to do this in google sheet ? thanks Replace below: =OR(CELL(“col”)=COLUMN(),CELL(“row”)=ROW()) With below: =OR(CELL(“col”)=COLUMN(),CELL(“row”)=ROW()) If you look closely you see it is a different type of quotation mark: “ ” Let me know if it worked for you 🙂 My doubt is code is getting failed when its have background colour applied on the sheet thanks for your effort … it is very helpful. is one improvement possible – have the highlight work in the middle of cut-paste? an example … i am copying data from one person (row) to another person (other row) when i select row 3, cols b+c, and copy … the proper row and col b highlight. when i select a cell in row 7 col b to paste, (another person) the highlight does not change (since cutcopymode = false ) until i paste the data into the selected cell , then the highlight changes correctly. it would be helpful to see the highlight change when i selected the cell – before i paste the data – so i could be sure i had selected the right person/row. If you’re interested: =OU(CELLULE(“col”)=COLONNE();CELLULE(“row”)=LIGNE()) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.CutCopyMode = False Then Application.ScreenUpdating = “False” Application.Calculate Application.ScreenUpdating = “True” End If End Sub to force the screen to update, but I’m not sure why I had to do this. =O(CELDA(“columna”)=COLUMNA();CELDA(“fila”)=FILA()) Also, when typing in the conditional formatting code use ALL CAPS. 🙂 Thank you for this!! Is there a way to make this work with a dynamic selection ? I would like this to work after I paste in a new set of data/selection (The selection will have the same columns, but the rows varies. Thanks Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.CutCopyMode = False Then Application.Calculate End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.CutCopyMode = False Then Application.Calculate End If End Sub to the VBA i have added some lines. With Application .ScreenUpdating = False .EnableEvents = False .Calculate .ScreenUpdating = True .EnableEvents = True End With screenupdating will help speeding up the highlighting enableevents will prevent the triggering Worksheet_Change