Q:

VBA: Input SUM formula for background filled cells ONLY

I'm fairly new to VBA and I am attempting to write a piece of code that will insert a sum formula into the blue cells and will sum down until the next blue cell (see attached). The reason I need this to be done is because this will be a template for users who will be inserting a text file into this spread sheet so I want it to format the cells and add formulas so if they decide to add a new row it will recalculate the total automatically. Any help would be greatly appreciated!!! Let me know if you need more detail!

A:

Not real sure how the rest of your data looks past the screenshot, but something like this should work. I tried to comment as thoroughly as possible to help explain how it was done so you can learn more about how VBA works.
Sub SumBetweenBlues()

'declare your variables
Dim ws As Worksheet
Dim x As Long, y As Long, endRow As Long, startSum As Long, endSum As Long, xBlue as Long
Dim colL As String

'set the worksheet to work with (this can be changed if necessary)
Set ws = ActiveWorkbook.ActiveSheet

'set the color of blue to check for
xBlue = RGB(201, 234, 236)

'column where the sums will be put (C)
Const sumCol As Integer = 3

'first row
Const startRow As Integer = 2

'turns the column number into a letter for the formula
colL = colLetter(sumCol)

'determines the last used row and goes a bit past it since blues may/may not be blank themselves
endRow = ws.Cells(ws.Rows.Count, sumCol).End(xlUp).Row + 50

'loop through all the cells in the sum column
For x = startRow To endRow

    'checks if the cell is blue
    If ws.Cells(x, sumCol).Interior.Color = xBlue Then

        'set the start of the sum range to the cell after the blue cell
        startSum = x + 1

        'find the end of the sum range
        For y = startSum + 1 To endRow

            'checks if the cell is also blue
            If ws.Cells(y, sumCol).Interior.Color = xBlue Then

                'sets the end of the sum range to the cell before the blue cell
                endSum = y - 1
                Exit For

            End If
        Next y

        'so long as an endsum area was found, set the formula in the blue cell
        If endSum <> 0 Then
            ws.Cells(x, sumCol).Formula = "=SUM(" & colL & startSum & ":" & colL & endSum & ")"
        End If

        'skip all the non-blue cells inbetween
        x = y - 1

        'reset the start/end of the sum area
        startSum = 0
        endSum = 0

    End If

Next x

End Sub

'---------------------------------------------------------------------------

Function colLetter(intCol As Integer) As String
'this function turns column numbers into letters
Dim vArr: vArr = Split(Cells(1, intCol).Address(True, False), "$"): colLetter = vArr(0)
End Function

I would advise checking out the Help Center (https://stackoverflow.com/help) and reading some of the topics there, as posting a question like this without showing what you've tried will typically get closed pretty quickly.

