|
|
|
|
|
|
Excel
|
Highlight Current Row and Column
|
|
Ease of Use
|
Easy
|
Version tested with
|
2002
|
Submitted by:
|
Jacob Hilderbrand
|
Description:
|
This macro automatically highlights the row and column for the current active cell using an event procedure. Note that any fill colors on the sheet will be removed.
|
Discussion:
|
Suppose you have a table of tons of data. This macro will highlight the current row from column A to the active cell, and the current column from 1 to the active cell. This will easily identify what the active cell is, and make it easier to locate the data.
|
Code:
|
instructions for use
|
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RngRow As Range
Dim RngCol As Range
Dim RngFinal As Range
Dim Row As Long
Dim Col As Long
Cells.Interior.ColorIndex = xlNone
Row = Target.Row
Col = Target.Column
Set RngRow = Range("A" & Row, Target)
Set RngCol = Range(Cells(1, Col), Target)
Set RngFinal = Union(RngRow, RngCol)
RngFinal.Interior.ColorIndex = 6
End Sub
|
How to use:
|
- Open Excel.
- Press Alt+F11 to open the VBE.
- From the Project Explorer double click on one of the Sheets (i.e. Sheet1).
- Paste the code in the window that opens up.
- Close the VBE (Alt + Q or press the X in the top-right corner).
|
Test the code:
|
- Follow the instructions in the "How to use" section.
- In Excel goto the sheet you put the code in.
- Select a cell and see the area get highlighted.
- Note that with this macro as is, all other cell fill colors will be removed.
- Download the attachment for a working example.
|
Sample File:
|
SelectionChange.ZIP 7.02KB
|
Approved by mdmackillop
|
This entry has been viewed 614 times.
|
|