Working with the Active Cell

The ActiveCell property returns a Range object that represents the cell that is active. You can apply any of the properties or methods of a Range object to the active cell, as in the following example. While one or more worksheet cells may be selected, only one of the cells in the selection can be the ActiveCell.

Sub SetValue() 
 Worksheets("Sheet1").Activate 
 ActiveCell.Value = 35 
End Sub

Note You can work with the active cell only when the worksheet that it is on is the active sheet.

Moving the Active Cell

You can use the Range .Activate method to designate which cell is the active cell. For example, the following procedure makes B5 the active cell and then formats it as bold.

Sub SetActive_MakeBold() 
 Worksheets("Sheet1").Activate 
 Worksheets("Sheet1").Range("B5").Activate 
 ActiveCell.Font.Bold = True 
End Sub

Note To select a range of cells, use the Select method. To make a single cell the active cell, use the Activate method.

You can use the Offset property to move the active cell. The following procedure inserts text into the active cell in the selected range and then moves the active cell one cell to the right without changing the selection.

Sub MoveActive() 
 Worksheets("Sheet1").Activate 
 Range("A1:D10").Select 
 ActiveCell.Value = "Monthly Totals" 
 ActiveCell.Offset(0, 1).Activate 
End Sub

Selecting the Cells Surrounding the Active Cell

The CurrentRegion property returns a range or 'island' of cells bounded by blank rows and columns. In the following example, the selection is expanded to include the cells that contain data immediately adjoining the active cell. This range is then formatted with the Currency style.

Sub Region() 
 Worksheets("Sheet1").Activate 
 ActiveCell.CurrentRegion.Select 
 Selection.Style = "Currency" 
End Sub