EXCEL – Highlight Active Row/Column with an Underlining Shape (VBA)

IMPORTANT NOTE: THE EXCEL FILE MUST BE SAVED AS A MACRO-ENABLED DOCUMENT (XLSM).

Think that you have loads of data with 6000 rows (or more, or less, it doesn’t matter) and you need to watch them daily for details. You might miss things after concentrating on it for too much, clicking on a row and reading the data from the row above or below. Want to avoid that without highlighting each row using the fill command, then removing highlight? You might possibly ruin highlight that you already have in your sheet.

Note: selecting multiple rows or columns won’t highlight all of them, the horizontal shape will be below the last row of the selection, and the vertical shape will be to the right of the last column of the selection (see animated image above).

The solution below allows you to highlight any row/column in the active sheet with the same horizontal and vertical shapes.

A. Draw the desired shapes and format them – the code below is set for lines, but you can also use rectangles and maybe other shapes too – feel free to explore

B. VBA code – this part is for moving the shapes you previously created

A. Draw the desired shapes and format them

1. Go to Insert – Shapes and pick the desired shape – I chose lines, but you can try almost anything

2. Insert a horizontal shape, then select it and in the top left field, above row 1 and column A, name your shape for example Line_Hor (you will use this name in your VBA code)

3. Insert a vertical line, then select it and in the top left field, above row 1 and column A, name it for example Line_Vert (you will use this name in your VBA code)

4. Format the lines above the way you want – color, width

B. VBA code

1. Right click the desired sheet, and select View Code to open the Microsoft Visual Basic for Applications window

2. In the window that opens paste the following lines of code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' declarations
Dim LR, LC As Long
Dim rng, rng2 As Range
Dim oShape_H, oShape_V As Shape

' you can name range manually, but this takes the entire used range in the active sheet
Set rng = ActiveSheet.UsedRange

' last row and last column in rng
LR = rng.Rows.Count + rng.Row - 1
LC = rng.Columns.Count + rng.Column - 1

' rng2 is the range starting from active cell row and column to last row and column of rng
With ActiveSheet
Set rng2 = .Range(.Cells(ActiveCell.Row, ActiveCell.Column), .Cells(LR, LC))
End With

' draw horizontal line and name it Line_Hor
Set oShape_H = ActiveSheet.Shapes("Line_Hor")
oShape_H.Width = rng2.Width
' draw vertical line and name it Line_Vert
Set oShape_V = ActiveSheet.Shapes("Line_Vert")
oShape_V.Height = rng2.Height

Dim oCells As Range
Set oCells = Selection

' position the start of Line_Hor below selected cells
oShape_H.Top = oCells.Top + oCells.Height + 2
oShape_H.Left = oCells.Left

' position the start of Line_Vert to the right of selected cells
oShape_V.Left = oCells.Left + oCells.Width + 2
oShape_V.Top = oCells.Top

Target.Calculate

End Sub

3. Close the Microsoft VBA window (not the Excel file)

4. Save the file as an Excel Macro-Enabled Workbook (.XLSM)

THAT’S IT!

 

Similar tutorials:

Highlight Active Row/Column when Cell is Selected – using conditional formatting

TBA – Highlight the active Row and Column left and above of the selected cell

TBA – Highlight the active Row and Column with colored borders

TBA – Highlight multiple Rows and Columns at once