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
        
    Application.Volatile
    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
   Continue:
     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.

This page last updated Wednesday, 16 February 2005

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

LongDec2Bin()

MultiCat()

PageSetupXL4M()

PrevSheet()

RandInt()

SampleNoReplace()

SumBold()

UnixToXLTime()

XL VBA Macros

Mac XL

Sum bold cells

Quick Links