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.
ActiveCell.Offset(1, 3).Font.Underline = xlDouble
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.
Dim counter As Integer
For counter = 1 To 20
Worksheets("Sheet1").Cells(counter, 3).Value = counter * 5