SUM if cell is Bold

S

steverossiter

Hi,

Is their a function / formula that will sum only cells that are
formatted in a particular way, such as Bold?

Thanks for the help, Steve
 
K

Ken Johnson

Hi Steve,

I think you need to use a User Defined Function to do that...

Public Function SumBold(rngSumRange As Range) As Single
Dim rngCell As Range
For Each rngCell In rngSumRange
If IsNumeric(rngCell.Value) Then
If rngCell.Font.Bold = True Then
SumBold = SumBold + rngCell.Value
End If
End If
Next rngCell
End Function

To get the code in place...

1. Copy it

2. In Excel go Tools|Macro|"Visual Basic Editor" or press Alt + F11 to
get into the Visual Basic Editor

3. In the Visual BAsic Editor go Insert|Module then paste the code into
the module that appears.

4. Save then go File|"Close and Return to Microsoft Excel" to get back
to Excel

5. If your Security is set at High or Very High change it to Medium by
going Tools|Macro|Security... then select Medium. Then close and reopen
and click on "Enable Macros" on the "Security Warning" dialog. UDF's
don't work unless security is Medium or Low.

Ken Johnson
 
S

steverossiter

You're welcome.
Hope it works out OK.
Click to expand...
The functions works nicely. Is it possible to have it update the sum
real-time? At present it sums bolded items that are bolded before they
were included in the range but not those that are in the range and
changed to bold after the range has already been set.

Thanks again, Steve.
 
K

Ken Johnson

Hi Steve,

I've been experimenting with different improvements. The problem is
that when you change a font to or from bold, Excel doesn't treat that
as a change requiring recalculation of formulas.

If you make the function volatile it will be calculated whenever Excel
carries out a calculation.
The following is the volatile version...


Public Function SumBold(rngSumRange As Range) As Single
Application.Volatile
Dim rngCell As Range
For Each rngCell In rngSumRange
If IsNumeric(rngCell.Value) Then
If rngCell.Font.Bold = True Then
SumBold = SumBold + rngCell.Value
End If
End If
Next rngCell
End Function

You will notice it is just the addition of "Application.Volatile" at
the beginning of the code.

An additional improvement is to increase the rate of calculations by
pasting this code into the worksheet's code module...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub
To get this code in place...

1. Copy the three lines
2. RightClick the Worksheet's tab, then select "View Code" from the
PopUp menu that appears. This will take you to the Visual Basic Editor
3. Paste the code into the blank code module
4. Press Alt + F11 to return to Excel

With this additional code in place, every time the user selects a
different cell the whole worksheet (maybe even the whole workbook) is
calculated. This part of the solution is going to be a bummer if your
worksheet takes a while to complete its calculations. I would leave
this part out if that is the case.

With both solutions in place the SumBold UDF will calculate when either
F9 is pressed or any other change occurs that triggers calculation or
the user selects a different cell.

I know it's not perfect, but I think it is the best that can be done
with a UDF that can't detect the change it relies on for calculation.

Ken Johnson
 
S

steverossiter

I know it's not perfect, but I think it is the best that can be done
with a UDF that can't detect the change it relies on for calculation.
Click to expand...

This will work nicely since the worksheet involves summing less than 50
items. Thank you for this refinement.

Steve
 
Top