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.
    excelhighlightrow1
  2. Define a new style named „Highlight“. This style will be used for an entire row.
    • Select menu item Format / Style.
      excelhighlightrow2
    • In Style dialog fill in name „Highligh“, uncheck Number, Alignment and Protection checkboxes.
      excelhighlightrow3
    • 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)
      excelhighlightrow4
    • Then in code editor select Worksheet from Object drop-down box and SelectionChange from Procedure drop-down box.
      excelhighlightrow5
    • 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

Tags: . This entry was posted on Monday, January 19th, 2009 at 8:29 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

One Response to “en: Highlight current row in Excel using VBA”

  • Claire says: (February 12, 2010 at 10:36 pm) Reply

    Fab, just what I have been looking for, thanks

Leave a Reply

Sidebar decoration image
Copyright © 2001-2009 Virtage Software. Virtage is registered trademark of Libor Jelinek.
Developer blogs and tutorials Virtage Developer is proudly powered by WordPress.