Get Total of Specific Background Colored Cells

Here is a VBA solution. I put the totals in column E. A note of information, the color index for cell D7 should be 40

Option Explicit

Sub ColorTotal()
    Application.ScreenUpdating = False
    Dim c As Range
    Dim rng As Range
    Dim i As Long, x As Long
    Dim lr As Long, lr2 As Long, lc As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    lr2 = Range("f" & Rows.Count).End(xlUp).Row
    lc = Cells(9, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(9, 6), Cells(lr2, lc))
    For i = 6 To lr
        x = 0
        For Each c In rng
            If c.Interior.ColorIndex = Range("D" & i) Then
                x = x + 1
            End If
        Next c
        Range("E" & i) = x
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

Excel 2016 (Windows) 32 bit

	B	C	D	E
5			GetColor	Total
6	R	No clip	35	23
7	R	Light Clip	40	5
8	R	Heavy Clip	38	12

Sheet: Sheet1

Hello Alan,

Your code works perfectly.

As a side note, is a VB script the only way to go here? That is, can this be carried out via a formula?

Also, can you tell me why I keep getting "36" instead of "40" like you have shown in your solution?

Thanks again!

Here is the code I used to determine the Index color.

Sub FindColor()
MsgBox "Background " & Selection.Interior.ColorIndex
MsgBox "Font Color " & Selection.Font.ColorIndex
End Sub

I am not aware of a formula solution.