Sum or Count cells that have a specified Fill Color

CUSTOM COLOR FUNCTION:

This can be done with the aid of a Custom Function.  A custom function, also called a User Defined Function, is a function that we write ourselves using VBA

There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not
recalculate if you change color. Every time you change the color you will
need to recalculate or wait excel to recalculate...

It is possible to calculate data based on cell color in excel by

SUM

=ColorFunction(I12,G7:G12,TRUE)

COUNT

=ColorFunction(I12,G7:G12,FALSE)


<!===========Excel macro code start===========>

The Code:
Function SumColor(rColor As Range, rSumRange As Range)

''''''''''''''''''''''''''''''''''''''
'Written by Senthamaraikannan
'www.askannan.blogspot.com

'Sums cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
 Dim rCell As Range
 Dim iCol As Integer
 Dim vResult

 iCol = rColor.Interior.ColorIndex

  For Each rCell In rSumRange
   If rCell.Interior.ColorIndex = iCol Then
    vResult = WorksheetFunction.Sum(rCell) + vResult
   End If
  Next rCell

 SumColor = vResult
End Function

<!=========Excel macro code End===============>

Download Excel Example file here

Categories: Macro