Thread: Sum based on color

Hi all

Is there a way to sum the cells based on the background color and font color? I need it such a way that summing should be according to the cell where the formula is given (based on its background/font color). I wrote a macro where I have to address a separate cell to take color. Can this be modified (considering the colors of conditional formatting also)?

Function ColorSum(ColorTaken As Range, TheCells As Range, Optional TheFont As String)
  Dim ColorConsidered As Integer, ColorWiseTotal As Long, OneCell As Range
  Application.Volatile True
  ColorConsidered = IIf(UCase(TheFont) <> "T", ColorTaken.Interior.ColorIndex, ColorTaken.Font.ColorIndex)
  For Each OneCell In TheCells
     ColorWiseTotal = ColorWiseTotal + IIf(IIf(UCase(TheFont) <> "T", _
     OneCell.Interior.ColorIndex, OneCell.Font.ColorIndex) = ColorConsidered, OneCell.Value, 0)
  Next OneCell
  ColorSum = ColorWiseTotal
End Function

Any help is highly appreciated

regards

johnjohns

The thing with conditional formatting is it doen't actually change the cell interior colorindex. You can usually get round this by using the condition that you are using in the formatting as the basis for a conditional sum (SUMIF/SUMPRODUCT/SUMIFS) and ignore the colours altogether.

Dom

In that case, is there a solution for the balance? ie, can the formula cell itself be addressed for color matching?

Sorry, don't quite follow what you mean.

I understand that you can do filtering etc on colours in 2007 but imho it is really just pandering to something people do all too often that should really be avoided. Again I would suggest it would be simpler to do a conditional sum based on the same conditions that the formatting is based on unless you have a complex set up of a lot of different conditions. I'm not aware of an easy way to achieve what I think you are asking otherwise.

Dom

Here's an example:

Public Function ColourSum(sumRange As Range, sumWhat As Byte, myColour As Byte) As Double

Dim myRange As Range

For Each myRange In sumRange

    If sumWhat = 1 Then

        If myRange.Interior.ColorIndex = myColour Then ColourSum = ColourSum + myRange.Value
        
    ElseIf sumWhat = 2 Then
    
        If myRange.Font.ColorIndex = myColour Then ColourSum = ColourSum + myRange.Value

    End If
    
Next myRange

End Function

You would use it like:

=ColourSum(A2:A10,1,3) to sum values with a red background colour

=ColorSum(A2:A10,2,3) to sum values with a red font colour