Archive

Archive for February, 2012

Excel – Highlight Row & Columns of Currently selected cell

February 29, 2012 Leave a comment

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