Microsoft Exel is a powerful tool to handle tons of data. But often, I see people make mistakes because they switch between rows. These kind of mistakes occur because Excel does not highlight the row of the active cell. The cell that is selected is being indicated, but without real highlighting.
Excel: No row and column highlighting for the active cell
Recently, I was looking for real row highlighting in Excel. This means I wanted a background colour for the current row that was selected, so it was clearly marked which row and column was currently being edited. Ultimately, I found a solution using VB (Visual Basic). This is the result I achieved using a fairly easy piece of code:
Excel: Row and column highlighting for the active cell by using a background colour.
First, you want to enable the developer tab. To do so, go to File > Options and go to Customize the ribbon. In that list, tick the Developers box.
Now, close the options menu and open the developer tab; now click on Visual Basic. For each of the worksheets, paste the following VBA code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False 'Clear all cell colours Cells.Interior.ColorIndex = 0 With Target 'Row and column highlightingfor the active cell .EntireRow.Interior.ColorIndex = 19 .EntireColumn.Interior.ColorIndex = 19 End With Application.ScreenUpdating = True End Sub
Visual Basic code for a worksheet
As seen in the images above, this code will give a yellow background colour to indicate the row and column. If you want, you can choose your own colour, see the ColorIndex Property documentation on the Office Dev Center. A red highlighting colour would mean you change the numerical value to your preferred value, in this case 3.
.EntireRow.Interior.ColorIndex = 19 .EntireColumn.Interior.ColorIndex = 19
Save the code. Excel will prompt you to save your document as a Excel document with the .xlsm file extension. This type of file is in essence the same as a regular .xls(x) file, but with the addition of a vbaProject.bin file containing functions and macros.
Xlsm file Excel save dialog
If the file is saved, you successfully got what you wanted: row highlighting in Excel!
This guide should apply to Microsoft Excel 2007, Excel 2010, Excel 2013 and Excel 2016.
This worked really well, thank you for publishing it. I have found an issue though. If I need to highlight a cell, as soon as I click another cell all my highlights disappear. How would I be able to keep the highlights?
Hi Doug
Unfortunately, I had the same issue but could not find a solution this for. In order to dynamically highlight rows and columns in Excel, all the other highlights are reset to a white background.
Doug,
The highlights worked well on my end as well. however, I have the exact same problem as Thomas. Once highlighted, it removes all the color coding in my cells.
Miko Dee
This is awesome! Thank you for your help!
-Raymond
The code works great. However, i encountered a problem with the undo function. I seems to have been disabled. Is there any fix for this?
Hi Nick
Unfortunately, since VB is now enabled and active, this results into the fact that the undo function is no longer functioning as before.
So far, I have yet to find a solution so the history works again.
Hello, this article is very informative and well-written, in a way that is easy to follow for a novice Excel user, such as myself.
I have a quick question and hope that someone may be able to answer. It seems like one would need to create the VB script for every Excel document they are working on in order to achieve the “row & column highlight” capability? Thank you very much!
This really help. Thanks alot!
Hi … a great easy to follow tutorial …
Had an issue, however … My worksheet is a bit large (like 70 columns & 590 rows, 90% numbers) so I have noticed that switching between cells is very laggy like 7 seconds ! Any solutions ?
Hi,
I need this to work on multiple tabs within the same workbook but it doesn’t seem to want to. Can you give me any advice? The formula is present on all pages. But it only seems to work on the very first one.
Love this so much.
Thank you for taking the time to post this and in such detail.
I know nothing about formulas and such.
Yes, it has a couple glitches, you just need to decide what is more important for you to have.
I love knowing which line I am on so I will make do without having the “Undo” available.
If anyone figures out how to make this work for more than one worksheet, please let me know.
Thanks again.
Most Awesome
This is amazing. Thank you!
Sir thanks for both cursor select shortcut, but when i run Visual Basic code for row and column highlighting in Excel 2010 so my worksheet background color why removed……
Thank you very much
u save my day
work like a charm
Thank you very much.
Thorough and easy to follow.
Thank you so much!!! I was damaging my eyesight trying to understand what row I was in, now it is all clear!
Thanks,
I have other questions. How to change the color of highlight? And how to get the highlight showing the grid of cells?
The macros worked well upon first install but a month or two later and now it doesn’t work. I’ve attempted to reinstall but still no luck. Any ideas?