Import formula from variable - Excel VBA

Question

Using the below code i m trying to loop column and rows. When cells in green import the range in the formula string. At row 7 i want to import a formula which sum up the green cells but i want the formula to be shown. the loop works fine, the formula create fine but is imported as string instead of a formula.

Any help will appreciated.

Sub test()

    Dim Row As Long
    Dim Col As Long
    Dim strFormula As String
    
    With ThisWorkbook.Worksheets("Sheet1")
        'Loop columns
        For Col = 1 To 3
            'Clear strFormula variable
            strFormula = ""
            'Loop rows
            For Row = 1 To 5
                'Check if cell is green
                If .Cells(Row, Col).Interior.Color = 9359529 Then
                    'Create formula
                    If strFormula = "" Then
                        strFormula = """=SUM(.cells(" & Row & "," & Col & ")"
                    Else
                        strFormula = strFormula & ",.cells(" & Row & "," & Col & ")"
                    End If
                Else
                End If
                
            Next Row
            
            'Finalize formula
            If strFormula <> "" Then
                strFormula = strFormula & ")"""
                'Import formula
                .Cells(7, Col).Formula = strFormula
            Else
            End If
            
        Next Col
    
    End With
        
End Sub

Answer 1

Please do not name your variable as Row. Row is a property and this will interfere with that.

I would do this slightly different. I would identify the range which has the relevant color and construct a range object which will have only those cells. In the end I will use it with an apostrophe ' to enter the formula as text.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rw As Long
    Dim cl As Long
    Dim rngGreen As Range
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    With ws
        For cl = 1 To 3
            Set rngGreen = Nothing
            
            For rw = 1 To 5
                If .Cells(rw, cl).Interior.Color = 9359529 Then
                    If rngGreen Is Nothing Then
                        Set rngGreen = .Cells(rw, cl)
                    Else
                        Set rngGreen = Union(.Cells(rw, cl), rngGreen)
                    End If
                End If
            Next rw
            
            If Not rngGreen Is Nothing Then .Cells(7, cl).Formula = _
            "'=Sum(" & rngGreen.Address & ")"
        Next cl
    End With
End Sub

Note: To get the sum, you can use "=Sum(" & rngGreen.Address & ")" without the apostrophe.