Home > Office > Excel – Highlight Row & Columns of Currently selected cell

Excel – Highlight Row & Columns of Currently selected cell

Problem:

In large worksheets it is very difficult to track the current row and column of the currently selected cell.

Solution:

Found a nice solution here

Dim RowRng As Long
Dim ColRng As Long

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If RowRng <> 0 Then
        With Rows(RowRng).Interior    ‘begin clearing previous highlights
            .Pattern = xlNone
        End With
        With Columns(ColRng).Interior
            .Pattern = xlNone
        End With
    End If
   
    RowRng = Target.Row
    ColRng = Target.Column
    With Rows(RowRng).Interior       ‘set highlights
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
    End With
    With Columns(ColRng).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
    End With
End Sub

This works as expected. Sample screen is shown below…

image

Advertisements
Categories: Office
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: