Highlight Active Cell’s Row and Column

Posted: May 6, 2013 by Transformer in Excel, VBA
Tags: , ,

Sometimes when we have a large amount of data in a spreadsheet then it is very difficult to identify which column and row we are looking at once we start scrolling. What if we could simply click a cell and it’s row and column are somehow highlighted so that we don’t need to strain our eye?

Highlight Active Cells Row & Column

Highlight Active Cells Row & Column

To highlight the row and column of the selected cell in a sheet, following code can be used:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

     Application.EnableEvents = False
     Range(Target.EntireRow.Address & "," & Target.EntireColumn.Address).Select
     Target.Activate
     Application.EnableEvents = True

End Sub

The above code will work for the worksheet that you write it for. If you want to see this behaviour in all the sheets in a workbook then similar code can be written on Workbook_SheetSelectionChange event in the Workbook.


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

    Application.EnableEvents = False
    Range(Target.EntireRow.Address & "," & Target.EntireColumn.Address).Select
    Target.Activate
    Application.EnableEvents = True

End Sub
Advertisements
Comments
  1. Calm Chen says:

    Thanks. So far the only problem is: When hit delete, it will delete all content on the highlighter row and column. Therefore, have to use backspace.

Share your thoughts/feedback

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