Highlight Selected Cell Row and Column in Microsoft Excel?
This article will show you a step-by-step guide on how to highlight selected cell row and column on Windows computers.
We are going to use VBA method which is quite easy to setup in your current excel workbook.
For instance, this is always a hassle when working with large worksheets, which has a lot of rows and columns in it and you may eventually lose your mouse cursor and can’t identify on which data you are looking at on the current worksheet.
Therefore, this post will give guidance on how to handle seeing highlighted cell row and column in Microsoft excel.
[Read: How to Highlight Duplicate Values in Excel Sheet?]
Highlight Active Row and Column in Excel using VBA
The below custom code needs to be setup with excel VBA.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
'Clear the color of all cells
Cells.Interior.ColorIndex = 0
With Target
'Highlight row and column of the selected cell
.EntireRow.Interior.ColorIndex = 38
.EntireColumn.Interior.ColorIndex = 24
End With
Application.ScreenUpdating = True
End Sub
How to add VBA code in Microsoft Excel to highlight row and column?
The above code can be executed silently on any of your specific worksheets.
Follow the steps below to insert the VBA code into the current excel worksheet:
- Open the workbook in which you want to add the code
- To get the VBA editor, use a keyboard combination Alt + F11
- You need to look for your target workbook from the left pane and double click to open it.
- Copy the code from above and paste it on this blank page
- Save it.
Now go back to your worksheet and select any of the cells. You will notice that the entire row and column will be highlighted with color for the selected cell.
Disadvantages:
However, this will sort out your problem to determine which cell you were on as it can highlight row and column for the selected cell but, there are below drawbacks applying this solution:
- This approach will remove your custom formatting, so if you have any colored cells, avoid using it. All worksheet cells have their backgrounds cleared with this code.
- When this code is run, the sheet’s undo functionality is blocked, and moving forward Ctrl + Z cannot be used to reverse an incorrect action.
In Microsoft Excel, this is how you can highlight the column and row of a selected cell. I appreciate you taking the time to read this post, and I hope this resolved your concern.
Highlight Active Row and Column in Excel using VBA Click To Tweet
