Highlight active row and column

There are several ways to accomplish highlighting the selected row and column in Excel. The approach described here enables lightning fast row and column highlighting while requiring only two lines of code to execute when selection changes occur.

All of the code necessary for this to work is included in this post, no third party plugins or dependencies are required.

Create Admin Worksheet

Create a new worksheet and name it whatever you want, I will name mine Admin. You can hide this worksheet as it will not need to be visible. It will be used to store the selected row and column variables for each worksheet you wish to have dynamic highlighting.

Create Named Ranges

In the new Admin worksheet, designate two cells, one for the selected row and one for the selected column for each sheet. You can easily set a named range by clicking on the desired cell, then typing a name that best represents the selected cell into the Name Box.

Select cell B2, then type Sheet1_Row into the Name Box overwriting “B2”. Finally, select B3, then type Sheet1_Col into the Name Box.

Alternatively, Named Ranges can be created via the “Name Manager” in the Formulas tab in the Excel ribbon.

VBA Sheet Code

Use Worksheet_SelectionChange to track selection changes in Sheet1. Below are the only two lines of code required to trigger the highlights to reflect the newly selected row and column.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    'Use Named Ranges to track the selected row & column
    [Sheet1_Row] = Target.Row
    [Sheet1_Col] = Target.Column

End Sub

VBA Module Code

There are two functions and one sub procedure needed to fully automate the process:

  1. Sub Create_Sheet1_Highlight
    • Created for Sheet1 to detect the range bounds to be highlighted
    • Calls the format function to add the conditional formulas for highlighting
  2. Public Function Create_Row_Col_Highlight_Conditional_Formats
    • Dynamically populates the conditional formulas into the passed row and column bounds
    • Should be executed whenever there is a change in the number or rows or columns in the sheet
    • If the data is static, it does not need to re-executed
  3. Public Function Col_Letter
    • Simple function to convert a column number to a letter
    • Needed to pass the start cell column letter to the conditional formulas
    • See more information in my Convert column number to letter post
Sub Create_Sheet1_Highlight()
Dim Dest As Worksheet, StartRow As Long, StartCol As Long, LastRow As Long, LastCol As Long
Set Dest = ThisWorkbook.Sheets("Sheet1")

    'Use column A to determine the Last Row
    LastRow = Dest.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Use row 1 to determine the Last Column
    LastCol = Dest.Cells(1, Dest.Columns.Count).End(xlToLeft).Column

    'Create the Conditional Formats
    Create_Row_Col_Highlight_Conditional_Formats Dest, 2, 1, LastRow, LastCol

End Sub

Public Function Create_Row_Col_Highlight_Conditional_Formats(Dest As Worksheet, StartRow As Long, StartCol As Long, LastRow As Long, LastCol As Long)
    
    'Ensure the Destination worksheet is active
    With Dest
        .Visible = xlSheetVisible
        .Select
    End With
    
    'Set Start Column Letter
    Dim StartColL As String, StartColNumber As Long
    StartColNumber = StartCol
    StartColLetter = Col_Letter(StartCol)
    
    'Set Color
    Dim HighlightColor As Long
    HighlightColor = 12709887
    
    'Add [Row] Highlight
    With Dest.Range(Cells(StartRow, StartColNumber), Cells(LastRow, LastCol))
        .FormatConditions.Delete 'Clear All Existing Conditional Formats in Destination Range
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ROW(" & StartColLetter & StartRow & ")=" & Dest.Name & "_Row"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = HighlightColor
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

    'Add [Column] Highlight
    With Dest.Range(Cells(StartRow, StartColNumber), Cells(LastRow, LastCol))
        .FormatConditions.Add Type:=xlExpression, Formula1:="=COLUMN(" & StartColLetter & StartRow & ")=" & Dest.Name & "_Col"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = HighlightColor
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

End Function

Public Function Col_Letter(ColNum As Long) As String
Dim MyByte As Byte, MyLetter As String
    Do
        MyByte = ((ColNum - 1) Mod 26)
        MyLetter = Chr(MyByte + 65) & MyLetter
        ColNum = (ColNum - MyByte) \ 26
    Loop While ColNum > 0
    Col_Letter = MyLetter
End Function

Let’s try it!

Populate Sheet1 with some dummy data, as many rows and columns as you would like.

Next, execute the Create_Sheet1_Highlight sub procedure to create the conditional formulas needed to enable dynamic row and column highlighting. The sheet code will update the row and column named ranges to reflect the new selected cell. The conditional formulas will then read the named range values and toggle on and off the row and column highlights automatically.

That’s it!

Download Working Example

Download Example
Tagged: