I need to activate a function through VBA code

[ouadad]

Unfortunately I can't figure out how to place a screenshot of my spreadsheet to show you what I'm doing, so I'll try to describe it (I tried Mr Excel HTML Maker, but I keep ending an error in the code when I use User Defined Selection).

I recorded and edited a macro (below) that looks at the at the colour of the first cell in a column, then calls a colorfunction to count the number of cells in a section of each row that has the same colour as the first cell. The problem occurs when I fill-down the formula for the colorfunction. All I get is #VALUE ! in each cell. It only seems to work if I manually fill-down the formula.

Any ideas how to activate the formula using VBA code?

Sub Macro1()
'
'   SES
'
    range("E2").Select
    ActiveCell.FormulaR1C1 = "=colorfunction(R1C5,RC[-3]:RC[-1],FALSE)"
    range("E2:E13240").Select
    Selection.FillDown
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'
'   MA
'
    range("J2").Select
    ActiveCell.FormulaR1C1 = "=colorfunction(R1C10,RC[-3]:RC[-1],FALSE)"
    range("j2:j13240").Select
    Selection.FillDown
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub

 

[mikerickson]

Have you tried code that avoids selection?

With Range("E2:E13240")
        .FormulaR1C1 = "=colorfunction(R1C5,RC[-3]:RC[-1],FALSE)"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Also have you tried explicitly calling Application.Calculate?

If none of that helps, what is the code for colorfunction?

 

[ouadad]

That did it ... thanks