Referring to Cells Relative to Other Cells

See Also

A common way to work with a cell relative to another cell is to use the Offset property. In the following example, the contents of the cell that's one row down and three columns over from the active cell on the active worksheet are formatted as double-underlined.

Sub Underline()
    ActiveCell.Offset(1, 3).Font.Underline = xlDouble
End Sub

Note You can record macros that use the Offset property instead of absolute references. On the Tools menu, point to Record Macro, and then click Use Relative References.

To loop through a range of cells, use a variable with the Cells property in a loop. The following example fills the first 20 cells in the third column with values between 5 and 100, incremented by 5. The variable counter is used as the row index for the Cells property.

Sub CycleThrough()
    Dim counter As Integer
    For counter = 1 To 20
        Worksheets("Sheet1").Cells(counter, 3).Value = counter * 5
    Next counter
End Sub