Page 189 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

Besides using the CurrentRegion property of the ActiveCell, you can also use the End method
to extend a range beyond the ActiveCell. When used with one of the values listed in Table 8-1,
Excel will extend a range using the same rules as when you use the End key combined with
one of the arrow keys to move to the end of a row or column.

The following example searches all of the time entries for one month in the Y2001ByMonth.xls
workbook for the highest entry. Once the highest entry is found, Excel extends the range
upwards and downwards to include all entries for that time period and changes the fill color
of the cells to blue.
Chapter 8

Sub HighLightTimeFrame()
Dim MyCell As Range, strAddress As String
Dim sngMaximum As Single

sngMaximum = 0
For Each MyCell In Range("D6:O36").Cells
If MyCell > sngMaximum Then
sngMaximum = MyCell
strAddress = MyCell.Address
End If
Next MyCell

Range(strAddress).Activate
Range(ActiveCell.End(xlUp), ActiveCell.End(xlDown)).Select
Selection.Cells.Interior.ColorIndex = 41
End Sub

163