Macro 30: Highlight the Active Row and Column
When looking at a table of numbers, it would be nice if Excel automatically highlighted the row and
column you’re on (as demonstrated in Figure 3-2). This effect gives your eyes a lead line up and down
the column as well as left and right across the row.
Figure 3-2: A highlighted row and column makes it easy to track data horizontally and vertically.
The following macro enables the effect you see in Figure 3-2 with just a simple double-click. When the
macro is in place, Excel highlights the row and column for the cell that is active, greatly improv- ing your
ability to view and edit a large grid.
How it works
Take a look at how this macro works:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
‘Step 1: Declare Variables
Dim strRange As String
‘Step2: Build the range string
strRange = Target.Cells.Address & “,” & _
Target.Cells.EntireColumn.Address & “,” & _
Target.Cells.EntireRow.Address
‘Step 3: Pass the range string to a Range
Range(strRange).Select
End Sub
1. We first declare an object called strRange. This creates a memory container we can use to
build a range string.
2. A range string is nothing more than the address for a range. “A1” is a range string that points to
cell A1. “A1:G5” is also a range string; this points to a range of cells encompassing cells A1 to G5.
In Step 2, we are building a range string that encompasses the double-clicked cell (called Target
in this macro), the entire active row, and the entire active column. The Address properties for
these three ranges are captured and pieced together into the strRange variable.
3. In Step 3, we feed the strRange variable as the address for a Range.Select statement.
This is the line of the code that finally highlights the double-clicked selection.
How to use it
To implement this macro, you need to copy and paste it into the Worksheet_BeforeDouble
Click event code window. Placing the macro there allows it to run each time you double-click on
the sheet.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click on the sheet from which you want to trigger the code.
4. Select the BeforeDoubleClick event from the Event drop-down list (see Figure 3-3).
5. Type or paste the code in the newly created module.
Figure 3-3: Type or paste your code into the Worksheet_BeforeDoubleClick event code window.
