Learn

Macro 30: Highlight the Active Row and Column

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.

learn
We will be happy to hear your thoughts

Leave a reply

Share knowledge
Learn
Logo
Enable registration in settings - general