Microsoft Office Tutorials and References
In Depth Information
Examining Worksheet Events
Figure 19-7: The Worksheet_Change procedure ensures that data validation doesn’t get deleted.
The SelectionChange event
The following procedure demonstrates the SelectionChange event. It’s executed whenever
the user makes a new selection on the worksheet.
Private Sub Worksheet_SelectionChange(ByVal Target _
Cells.Interior.ColorIndex = xlNone
.EntireRow.Interior.Color = RGB(219, 229, 241)
.EntireColumn.Interior.Color = RGB(219, 229, 241)
This procedure shades the row and column of the active cell, which makes identifying the active
cell very easy. The first statement removes the background color for all cells in the worksheet.
Next, the entire row and column of the active cell is shaded light blue. Figure 19-8 shows the
shading in effect.
You won’t want to use the procedure if your worksheet contains any background shading
because the shading will be wiped out. The exceptions are tables with a style applied and
background colors resulting from conditional formatting. In both of these instances, the background
color is maintained. Keep in mind, however, that executing the Worksheet_SelectionChange
macro destroys the Undo stack, so using this technique essentially disables Excel’s Undo feature.
This example, named shade active row and column.xlsm , is available on the