Friday, March 30, 2012

VBA Excel: Highlight Current Row and Column


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:

 
  

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:
  1. Open Excel.
  2. Press Alt+F11 to open the VBE.
  3. From the Project Explorer double click on one of the Sheets (i.e. Sheet1).
  4. Paste the code in the window that opens up.
  5. Close the VBE (Alt + Q or press the X in the top-right corner).
 
Test the code:
  1. Follow the instructions in the "How to use" section.
  2. In Excel goto the sheet you put the code in.
  3. Select a cell and see the area get highlighted.
  4. Note that with this macro as is, all other cell fill colors will be removed.
  5. Download the attachment for a working example.
 
Sample File:

Post by at
Tags: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)