Microsoft Excel: Highlight the current row and column

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

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 color.

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

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

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.

 

Author Bio

Thank you for your interest in my blog! On this miniblog, I write mostly short (technical) blog posts that might interest other people. Read more about me or feel free to contact me.

 

17 thoughts on “Microsoft Excel: Highlight the current row and column

  1. 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?

    1. 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.

      1. 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

    1. 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.

  2. 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!

  3. 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 ?

  4. 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.

  5. 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

  6. 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……

Leave a Reply

Your email address will not be published. Required fields are marked *