Excel 2019 Bible

Chapter 46: Working with Excel Events

46

change the procedure to loop through all of the cells in Target. The following procedure

checks each changed cell and displays a message box if the cell is within the desired range:

Private Sub Worksheet_Change(ByVal Target As Range)

Set VRange = Me.Range("InputRange")

For Each cell In Target.Cells

If Union(cell, VRange).Address = VRange.Address Then

Msgbox "The changed cell is in the input range."

End if

Next cell

End Sub

A workbook with this example is available on this book’s website at http://www.wiley.com/go/excel2019bible.
The file is named monitor a range.xlsm.

Using the SelectionChange event

The following procedure demonstrates a SelectionChange event. It executes whenever

the user makes a new selection on the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Cells.Interior.ColorIndex = xlNone

With Target

.EntireRow.Interior.ColorIndex = 35

.EntireColumn.Interior.ColorIndex = 35

End With

End Sub

This procedure shades the row and column of the selected cells, making it easy to identify.

The first statement removes the background color of all cells. Next, the entire rows and col-

umns of the selected cells are shaded light green. Figure 46.2 shows the shading.

A workbook with this example is available on this book’s website at http://www.wiley.com/go/excel2019bible.
The file is named selection change event.xlsm.

You won’t want to use this procedure if your worksheet contains background shading because the macro will erase
it. However, if the shading is the result of a style applied to a table, the macro doesn’t erase the table’s background
shading.