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
FORMULA CODE IS
=SUMBYCOLOR(**,??????)TRUE
Sample: I have done a simple one for you to see how it can be used when doing your own spreadsheet.
DOWNLOAD
