en: Highlight current row in Excel using VBA

Highlighing currently edited row for better user experience that maybe someday buil-in in Excel, meanwhile you must use Visual Basic for Application code to do this. It’s very helpful especially in very wide long rows. In this tutorial I will show you how to employ Excel event model to highligh current row.

Download the example workbook with highlighing current row.

1. Firstly, format some cell like a highlighted one should looks like using different background color, font weight, font color, anything you want. Here, cell A4 is formated like highlighted.
   
2. Define a new style named „Highlight“. This style will be used for an entire row.
   
   • Select menu item Format / Style.
     
   • In Style dialog fill in name „Highligh“, uncheck Number, Alignment and Protection checkboxes.
     
   • Click Add button.
   • Now we have two styles: Highlight for current row, and Normal for the rest of worksheet.
   
   
   
   
3. Open VBA editor by pressing Alt+F11 or Tools / Marco / Visual Basic Editor.
4. Create new Worksheet_SelectionChange event handler.
   
   • Double-click in left pane to sheet and file where you want to highlight row (e.g. sheet Sheet1 in file Book3.xls)
     
   • Then in code editor select Worksheet from Object drop-down box and SelectionChange from Procedure drop-down box.
     
   • VBA will create blank Worksheet_SelectionChange procedure acting like SelectionChange event handler.
   
   
   
   
5. Paste the code. SelectionChange event will fire everytime you change cell (by arrow key, mouse, click to row/column header). In this case, we want to highlight a row. Paste following source code to the body of handler.

' Highlight only if selection change was made inside the content range (A2:E50)
If Not Intersect(Target, Range("A2:E50")) Is Nothing Then
  ' Turn off screen updating for better performace
  Application.ScreenUpdating = False
 
  ' Clear style of content range (A2:E50)
  Range("A2:E50").Style = "Normal"
 
  ' Set highligh style to current row
  ' (will Target.Row contains current row number)
  Range("A" & Target.Row & ":E" & Target.Row).Style = "Highlight"
 
  ' Turn on screen updating
  Application.ScreenUpdating = True
End If