AutoSum based on FILL color?

Copper Contributor



     Is there a way to run an autosum on a row with various colored cells and to only sum up a particular color?  For example, I have numbers in 10 cells:  A1-A10.  I have made cells A3 & A6  the same fill color and want to know their combined sum.  Can I create either an if/then or an autosum (or a combination of the two) for this?  I realize on an example this small it would be easier to add individual cells, but if I had data in 1000 cells with 97 of them a particular fill color...


   Thank you!

1 Reply


Public Function SumBackGroundColor(Selection As Range) As Long

    Dim i As Long
    Dim cell As Range
    For Each cell In Selection
    If cell.Interior.ColorIndex <> -4142 Then
    i = i + cell.Value

    End If
    Next cell
    SumBackGroundColor = i
End Function

You can try this function which sums the values of cells if the background color isn't white.


In the attached file you can enter


and select a range with the mouse for which you want to sum the values of cells with background color and then press enter.

sum cells with background color.JPG