Excel – VBA – Highlight Current Row

For my own needs, I wanted to highlight the active row the user selected to make it standout from the rest of the worksheet. Below is a simple procedure that does this nicely.

The Basic Concept

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lLastCol As Long
    Dim lLastRow As Long
    Const lFirstRow = 1
    Const lFirstCol = 1
    
    On Error GoTo Error_Handler

    lLastCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    Range(Cells(lFirstRow, lFirstCol), Cells(lLastRow, lLastCol)).Interior.Color = vbWhite
    Range(Cells(Target.Row, lFirstCol), Cells(Target.Row, lLastCol)).Interior.Color = vbYellow

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Worksheet_SelectionChange" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

You can change the color to suit your needs by simply changing vbWhite/vbYellow as you see fit.

Concept Further Extended

Building upon Sebastien’s comment/code I have put together the following demo/download.

Disclaimer

All code samples, download samples, links, … on this site are provided ‘AS IS‘.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

Download a Demo Workbook

Feel free to download a 100% unlocked demo copy by using the link provided below:

Download “Excel - Highlight Active Row in WorkSheet” HighlightActiveRow.zip – Downloaded 5429 times – 16.21 KB

Version History

V1.000 (2019-04-07)

2 responses on “Excel – VBA – Highlight Current Row”

  1. esebastien

    ‘????
    Dim RngOldTarget As Range
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lLastCol As Long
    Dim lLastRow As Long

    Const lFirstRow = 1
    Const lFirstCol = 1

    On Error GoTo Error_Handler

    lLastCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, “A”).End(xlUp).Row

    Range(Cells(lFirstRow, lFirstCol), Cells(lFirstRow, lFirstCol)).Interior.Color = RGB(125, 125, 125)

    Range(Cells(RngOldTarget.Row, lFirstCol), Cells(RngOldTarget.Row, lLastCol)).Interior.Color = vbNull
    Range(Cells(Target.Row, lFirstCol), Cells(Target.Row, lLastCol)).Interior.Color = vbBlue

    Set RngOldTarget = Target

    Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

    Error_Handler:
    MsgBox “The following error has occured” & vbCrLf & vbCrLf & _
    “Error Number: ” & Err.Number & vbCrLf & _
    “Error Source: Worksheet_SelectionChange” & vbCrLf & _
    “Error Description: ” & Err.Description & _
    Switch(Erl = 0, “”, Erl 0, vbCrLf & “Line No: ” & Erl), _
    vbOKOnly + vbCritical, “An Error has Occured!”
    Resume Error_Handler_Exit
    End Sub

    thanks

    1. Daniel Pineault Post author

      I like the idea, but there’s still an issue when closing the workbook it will leave the last row highlighted which can cause an issue. So we have to add to your code a Workbook_BeforeClose event to remove the last highlight row.