Try the below. Your formula is not in R1C1 syntax

Cells(i, 12).Formula = "=SUM(J" & StartCount & ":J" & EndCount & ")"

If this post helps click Yes
---------------
Jacob Skaria


"swiftcode" wrote:

Hi all,

i have a problem with the following code, would appreciate if anyone can
give me some help on this. Basically everything works fine except for this
line:

Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")"

The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)".

---------------------------------------------------------------------------------------------
Sub Test()

For i = 2 To 100 Step 1
Cells(i, 1).Select
If Cells(i, 1).Interior.ColorIndex = 2 Then
StartCount = ActiveCell.Row

ElseIf Cells(i, 1).Interior.ColorIndex = 37 Then
EndCount = ActiveCell.Row
Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")"

End If
Next i

End Sub
----------------------------------------------------------------------------------------

Thanks in advance

Rgds
Ray