Hum, no, no.

The Option Explicit must always be the first instruction
Just under this instruction, you can have local private declarations...
And under these declarations, you put the set of subroutines and/or functions

This is the final version of the code that you must attach to the command button :

Option Explicit

Sub Eg_New()
'---------------------------------------------------------------------------
' Procedure : Eg_New

' Notice : Press F5 to run the macro
'---------------------------------------------------------------------------
'Constants :

'Color for bill rate column
Const BILL_RATE_CELL_COLOR As Long = 15849925
'Color for total spend column
Const TOTAL_SPEND_CELL_COLOR As Long = 12379352
'Value of the first row (could be over 1)
Const FIRST_ROW As Integer = 1
'Row start of data (FIRST_ROW must be ajusted)
Const TARGET_ROW As Integer = 3
'Number of columns between bill rate columns
Const COLUMN_OFFSET_BETWEEN_SUBTOTALS As Integer = 3
'Bill rate formula
Const BILL_RATE_FORMULA As String = "=IF(RC[-1]<>0,RC[-1]*RC[-3],RC[-2]*RC[-3])"

Dim oRng As Range
Dim C As Integer
Dim R As Long
Dim strRangeAddress As String
Dim intColumnIndex As Integer
Dim blnApplyConversion As Boolean
Dim lngLastRow As Long
Dim intLastColumn As Integer
Dim intUsedLastColumn As Integer

Dim intColPosition As Integer
Dim strTotalSpendFormula As String

On Error GoTo L_ErrEg
'Confirm execution...
If MsgBox("Do you want to apply formulas ?", vbQuestion + vbYesNo, "Confirm") = vbNo Then Exit Sub
'Freeze screen
Application.ScreenUpdating = False
'Get the working area
Cells(1, 1).Select
'last row
lngLastRow = ActiveCell.End(xlDown).Row
Cells(2, 1).Select
'True last column
intLastColumn = ActiveCell.End(xlToRight).Column
Cells(3, 1).Select
'Supposed last column
intUsedLastColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
'Delete unused columns
If intUsedLastColumn > intLastColumn Then
For C = intLastColumn + 1 To intUsedLastColumn
Columns(intLastColumn + 1).Delete
Next
End If
'For each row
For R = TARGET_ROW To lngLastRow
'For the 14 target cells (4 by 4)
For C = 10 To intLastColumn Step 4
'Build the address A1
strRangeAddress = Cells(R, C).Address(False, False)
'Set the range object
Set oRng = Range(strRangeAddress)
'Define the range formula and its features
With oRng
.FormulaR1C1 = BILL_RATE_FORMULA
.Interior.Color = BILL_RATE_CELL_COLOR
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
'Build Total Spend formula
intColPosition = intLastColumn - (C - COLUMN_OFFSET_BETWEEN_SUBTOTALS)
strTotalSpendFormula = strTotalSpendFormula & "RC[-" & intColPosition & "],"
Next C
'Put the Total Spend formula
If Right$(strTotalSpendFormula, 1) = "," Then strTotalSpendFormula = Left$(strTotalSpendFormula, Len(strTotalSpendFormula) - 1)
strTotalSpendFormula = "=SUM(" & strTotalSpendFormula & ")"
'Set the range object
strRangeAddress = Cells(R, C - 1).Address(False, False)
Set oRng = Range(strRangeAddress)
With oRng
.FormulaR1C1 = strTotalSpendFormula
.Interior.Color = TOTAL_SPEND_CELL_COLOR
.HorizontalAlignment = xlRight
.Font.Bold = True
End With
'Emptying formula variable
strTotalSpendFormula = vbNullString
Next R
'Put title
Cells(2, C - 1).Value = "Total Spend"
On Error GoTo 0
L_ExEg:
'Hide formulas...
ActiveWindow.DisplayFormulas = False
'Freeing objects
Set oRng = Nothing
Application.ScreenUpdating = True
Exit Sub

L_ErrEg:
MsgBox Err.Description, 48, Err.Source
Resume L_ExEg
End Sub