• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

7. Macro Hacks > 88. Count or Sum Cells That Have a Specified Fill Color

Count or Sum Cells That Have a Specified Fill Color

Using a bit of code, you can easily SUM or COUNT cells whose fill color was specified manually.

Every now and then, it’s convenient to SUM or COUNT cells that have a specified fill color that you or another user have set manually, as users often understand paint colors more readily than named ranges. To do this, first open the workbook where you want to COUNT or SUM cells by a fill color. Go into the VBE by selecting Tools Macro Visual Basic Editor (Alt/Option-F11) and then select Insert Module to insert a standard module. In this module, type the following code:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

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

PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint