Excel Color Count

So a week ago I was having issues getting totals based on color backgrounds in excel and found out that excel did not have a formula or an action button for me to sum up the color background of each colored cell.
This was going to make it difficult for me to know who I gave discounts to my clients or which clients were to be taking off my database.

Well with Virtual Basic you can copy/paste the necessary code to make that color sum work within Excel. Here are the instructions……


Microsoft Excel:When you Open a new workbook or spreadsheet be sure you do ALT+F11 to bring up the virtual Basic program, now go to insert, then Module

COPY AND PASTE THIS CODE BELOW TO THE MODULE  

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

    Dim rCell As Range

    Dim lCol As Long

    Dim vResult

''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com

'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

 

    lCol = rColor.Interior.ColorIndex

    If SUM = True Then

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = WorksheetFunction.SUM(rCell, vResult)

            End If

        Next rCell

    Else

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = 1 + vResult

            End If

        Next rCell

    End If

   ColorFunction = vResult

End Function

THE CODE ENDS HERE ,AFTER YOU PASTE IT DO ALT+F3

THEN ALT+Q

Now that you are back into the spreadsheet you can now use the necessary formula to sum up the color backgrounds or numbers in the spreadsheet.

FORMULA CODE IS

=SUMBYCOLOR(**,??????)TRUE

The * mean the cell were the color source is coming from.

Sample: I have done a simple one for you to see how it can be used when doing your own spreadsheet.

DOWNLOAD