Microsoft Office Tutorials and References
In Depth Information
This macro runs every time you move to a new cell in the worksheet. The
macro works for one worksheet.
Note: If you want it to work on all worksheets, put this code in the
SheetSelectionChange macro in the ThisWorkbook code pane.
Follow the instructions in “Create an Event Handler Macro” to open the
worksheet code module. Then paste in the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wi As Window
Set wi = ActiveWindow
Cells.Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 6
For i = wi.VisibleRange.Rows(1).Row To Target.Row - 1
Cells(i, Target.Column).Interior.ColorIndex = 36
Next i
For i = wi.VisibleRange.Columns(1).Column To Target.Column - 1
Cells(Target.Row, i).Interior.ColorIndex = 36
Next i
End Sub
Here is how the code works:
The fi rst line indicates that you will have access to a range variable called
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
This is an object variable, so it not only tells you the value of the active cell
( Target.Value or simply Target ), it can tell you information about the active
cell, such as Target.Row or Target.Column
The Dim and Set lines defi ne an objet variable to refer to the current window
in Excel:
Dim wi As Window
Set wi = ActiveWindow
While many macros refer to the current worksheet, you need to refer to the
active window here so you can capture the top row in the visible portion of the
This line resets the color of all cells to have no fi ll:
Cells.Interior.ColorIndex = xlNone
This erases all the yellow highlighting drawn in by the last running of the
macro. Because you don’t specify which cells, this resets all cells in the entire
Search JabSto ::

Custom Search