Referring to Cells Relative to Other Cells [Excel 2003 VBA Language Reference]

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 Macro, click Record New Macro, click OK, and then click the Relative Reference button, on the record macro toolbar.

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

See Also | Referring to Cells by Using a Range Object | Referring to Named Ranges