Adam K Dean

Excel SUM/COUNT based on background colour

Published on 24 July 2013 at 11:27 by Adam

Today I have had to use VB. It's been seven or eight years since I last touched it, and today it's only to briefly complete a solution in Excel. I miss my curly braces.

I've been asked how to calculate a SUM() based on cell background colour, and it looks like there isn't anything built in for this. My StackOverflow question didn't exactly bring any great ideas, so it looks like a VBA script was my only option.

Example

The following snippet -- inserted by pressing Alt + F11, then going to Insert and Module -- will SUM() based on the background colour of the cell passed in the first argument, the second argument being the range:

Function SUMCLR(rColor As Range, rRange As Range)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
    lCol = rColor.Interior.ColorIndex
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = WorksheetFunction.SUM(rCell, vResult)
        End If
    Next rCell
    SUMCLR = vResult
End Function

This next snippet performs a count, rather than a sum operation:

Function COUNTCLR(rColor As Range, rRange As Range)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
    lCol = rColor.Interior.ColorIndex
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = 1 + vResult
        End If
    Next rCell
    COUNTCLR = vResult
End Function

And then in the cells, just use the formula for example =SUMCLR(A1, B1:B10) and =COUNTCLR(A1, B1:B10).



This post was first published on 24 July 2013 at 11:27. It was filed under archive with tags excel, vba.