General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions


XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Sum bold cells

XL has no native functions that are based on cell formatting. This function will sum cells that have the bold formatting applied.

One problem is that there are no events that fire when cell formatting is changed. While using Application.Volatile causes the cell to recalculate any time a calculation is done on the worksheet, this means that the cell may not contain a valid value unless a manual calculation (e.g., F9) is performed.

Put this in a regular code module.

  'Purpose:   Sum cells with bold formatting applied
  'Inputs:     vInput - array of one or more input ranges
  'Returns:   Sum of cells in vInput with bold formatting
  Public Function SumBold( _
        ParamArray vInput() As Variant) As Variant
    Dim rParam As Variant
    Dim rCell As Range
    Dim vTemp As Variant
    On Error GoTo ErrHandler
    For Each rParam In vInput
      If TypeName(rParam) = "Range" Then
         With rParam
           For Each rCell In Intersect( _
               .Cells, .Cells.Parent.UsedRange)
             With rCell
               If .Font.Bold Then
                 If IsError(.Value) Then
                    vTemp = .Value
                    Exit For
                  ElseIf VarType(.Value2) = vbDouble Then
                    vTemp = vTemp + .Value2
                  End If
               End If
             End With
           Next rCell
         End With
       End If
     Next rParam
     SumBold = vTemp
     On Error GoTo 0
     Exit Function
   ErrHandler:             'Check for overflow
     If Err.Number = 6 Then SumBold = CVErr(xlErrNum)
     Resume Continue
   End Function

Thanks to Harlan Grove for pointing out that the .Value2 property was appropriate to use here, rather than .Value.

Valid XHTML 1.1Valid CSSMade on a Macintosh

Quick Links

Not familiar with User Defined Functions (UDFs)? See David McRitchie's Getting Started With Macros

Where to put your code

About Event Macros

Sum or Count based on colors