Microsoft Office Tutorials and References
In Depth Information
USE A MACRO TO HIGHLIGHT THE ACTIVE CELL
This line changes the color of the selection to bright yellow:
Target.Interior.ColorIndex = 6
It uses the old Excel 2003 concept of ColorIndex so that it will work in either
Excel 2003 or Excel 2007. Excel 2007 supports more than 56 colors, so you
can use the RGB function to return any of 16 million colors.
Target.Interior.Color = RGB(200,200,228)
Next, you draw in the crosshairs in a lighter yellow. This involves looping from
the top row in the visible section of the worksheet down to the row above
the selection. To ﬁ nd the top row of the visible section of the worksheet, use
wi.VisibleRange.Rows(1).Row . To ﬁ nd the row immediately above the
selection, use Target.Row – 1 . The following loop goes through each of the
cells from the selection up to the top of the visible worksheet:
For i = wi.VisibleRange.Rows(1).Row To Target.Row - 1
Cells(i, Target.Column). Interior.ColorIndex = 36
The line of code inside the loop colors the cell at the intersection of i and the
same column as the Target. 36 is the color code for light yellow.
Only a minor adjustment is needed to build a second loop to color in all the
cells in the current row from the left edge of the worksheet up to one column to
the left of the selection:
For i = wi.VisibleRange.Columns(1).Column To Target.Column - 1
Cells(Target.Row, i).Interior.ColorIndex = 36
You might be wondering what would happen if Target is the top row of the
visible window. Say that you select cell A11 in Figure 142. The macro clears all
the yellow formatting from all cells in the worksheet, resetting all cells back to
their original color. The macro then colors the Target cell bright yellow. This
The ﬁ rst line of the loop uses row 11 as the ﬁ rst row of the visible window. It
uses row 10 as the row above the target cell. When the loop says For i = 11
to 10 , Excel simply skips the loop. Nothing gets colored light yellow in the ﬁ rst
loop. Similarly, the second loop is skipped as Excel tries to loop from 1 to 0.
Excel does a very quick and smooth job of running event handler macros run.
As you move from cell to cell in the worksheet, Excel constantly redraws the
yellow highlights to help you ﬁ nd the active cell.
Summary: An event handler macro can help you keep track of the active