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