In Excel 97 or 2000 how can I create a formula where the
result is conditioned on the formatting of certain cells?  For
example, I have a column of numbers, some of which are bolded, and I
want to be able to add all the bolded cells, but not the unbolded
cells.

Hi tiger72-ga,

I've searched through the Excel help files and was unable to find a
function that would recognize bolding.  The closest function was
Cell() although that only worked with cell formatting (General,
Number, etc).

The solution I've come up with is based in Excel VBA and is quite
simple and does exactly what you've asked for.  Please follow the
steps below to add the User Defined Function to Excel:

1. In your Excel worksheet window, press Alt+F11.
2. In the new Microsoft Visual Basic window, click on the Insert menu,
Module.
3. In the right hand pane, in the window titled "Module1 (Code)",
paste in the following function:

Function SumBold(CellRange As Range)
    Dim sumb
    For Each cell In CellRange.Cells
        If cell.Font.Bold = True Then
            sumb = sumb + cell.Value
        End If
    Next
    SumBold = sumb
End Function

4. Press Alt+Q to save.

Now in any cell in this workbook, you can enter the formula =SumBold()
to have it add up all bolded numbers within a range.  You would use
=SumBold() in the exact way that you would use =SUM().

One caveat is that Excel does not appear to recognize bolding as an
event, so when you bold/unbold new items, the sum may not reflect the
new changes.  You'll have to use Ctrl+Alt+F9 to have it recalculate
all formulas on the sheet.

If you wish to use this new SumBold() formula in another workbook,
simply repeat the instructions above to transfer the SumBold()
formula.

Hope this helps,
mrbuzz-ga


Additional Information:

Excel User Define Function VBA. Custom Functions - User Defined
Functions
http://www.ozgrid.com/VBA/Functions.htm

Excel Pages - Calculation Secrets
http://www.decisionmodels.com/calcsecretsh.htm


Search Terms Used:
://www.google.com/search?q=excel+vba+recalculate
://www.google.com/search?q=excel+vba+custom+functions
://www.google.com/search?q=excel+vba+bold+format+function

---

Request for Answer Clarification by tiger72-ga on 02 Jan 2003 13:59 PST

Thanks, mrbuzz, you make it sound easy.  Would a similar approach work with italics?

---

Clarification of Answer by mrbuzz-ga on 02 Jan 2003 14:58 PST

Hi tiger72-ga,

I'm glad you like the approach. :)

Yes, for italics it's virtually the same code.  Simply change the
following line from:
If cell.Font.Bold = True Then

And change it to:
If cell.Font.Italic = True Then

You'll also have to change the name of the function.  So for a Sum of
Italics, the code would look like:

Function SumItalics(CellRange As Range) 
    Dim sumi
    For Each cell In CellRange.Cells 
        If cell.Font.Italic = True Then 
            sumi = sumi + cell.Value 
        End If 
    Next 
    SumItalics = sumi
End Function


If you're the adventurous type, you can check out what other
formatting can be detected through Excel VBA pressing F2 in the
Microsoft Visual Basic window, to bring up the Object Browser.  Then
in the first dropdown box that might say "<All Libraries>", choose
Excel.  Then in the left pane, scroll down to Font.  The right pane
now lists the complete set of properties that can be used in a user
defined function.

Good luck,
mrbuzz-ga

You may want to give Spreadsheet Assistant a try as it appears you can
select cells based upon format.  You can see and download a free trial
at http://www.office-addins.com/excel-add-ins/Spreadsheet-Assistant-2.html

-THV

Nicely done, mrbuzz!

-THV

Hehe, thanks tar_heel_v!  I've used VBA in other Office applications
before but it was my first time using it in Excel.  I learned quite a
bit myself.  :)

mrbuzz-ga