How to: Refer to Cells Relative to Other Cells
Published: July 16, 2012
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 is 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
You can record macros that use the Offset property to specify relative references instead of absolute references. To do that, on the Developer tab, click Use Relative References, and then click Record Macro.
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