Sub LoopThroughColumn()
'Loop through all of the cells in one column of a small table and add the RowNumber _
to the Sheep value and put the result in the cell to the right
Application.ScreenUpdating = False

Dim Counter As Integer
Dim FinalRow As Integer
Dim Header As Range
Dim NewId As Variant

Excel.Application.Workbooks("ExcelDemo.xlsm").Worksheets("SleepStudy").Activate

Set Header = ActiveSheet.Range("B1")

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For Counter = 0 To FinalRow - 2
	Set NewId = Header.Offset(Counter + 1, 1)
        With NewId
			.Value = Header.Offset(Counter + 1).Value + Counter + 1 _
				& "G" & WorksheetFunction.RandBetween(100, 999)
			.Font.ColorIndex = 25
		End With
    Next Counter
End Sub