AutoSum based on FILL color?

Copper Contributor

Friends-

 

     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

@JoshSchaf 

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
    Else

    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

=SumBackGroundColor( 

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