Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sum cell if bold (Excel 2000)

    I have a list of numbers. I only want to sum the numbers that have been formatted in bold. Is there a function that will sum, if bold? Thank you.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sum cell if bold (Excel 2000)

    Try this code:
    <pre>Function SumBold(rng As Range)
    Dim rCell As Range
    Application.Volatile
    SumBold = 0
    For Each rCell In rng
    If rCell.Font.Bold Then _
    SumBold = SumBold + rCell
    Next
    End Function</pre>

    I will quote a caveat from Chip Pearson (when he discussed summing cells of a particular color)
    <hr>NOTE: When you change the background or font color of a cell, Excel does not consider this to be changing the value of the cell. Therefore, it will not recalculate the worksheet, nor will it trigger a Worksheet_Change event procedure. This means that the values returned by these functions may not be correct immediately after you change the color of a cell. They will not return an updated value until you recalculate the worksheet by pressing ALT+F9 or by changing the actual value of a cell. There is no practical work-around to this. You could use the Worksheet_SelectionChange event procedure to force a calculation, but this could have a serious and detrimental impact on performance. <hr>
    This only works with explicit bolding, not conditional bolding.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum cell if bold (Excel 2000)

    I need more assistance. I am summing the bold cells from b1 to b50 and placing the answer in A50. Do I write a macro and if I write a macro with your function where does the macro recognize the summing of cells b1 to b50? Thank you.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum cell if bold (Excel 2000)

    Here are detailed instructions:
    - Select Steve's code (from Function ... up to and including End Function)
    - Copy it to the clipboard (Ctrl+C)
    - Paste it into a Word document. (Yes, this is necessary, strange as it may seem.)
    - Select the code you just pasted.
    - Copy it again.
    - Switch to your workbook in Excel.
    - Activate the Visual Basic Editor (Alt+F11)
    - Select Insert | Module.
    - Paste the code.
    - Switch back to your workbook (Alt+F11)
    - Enter the following formula in cell A50:

    <code>=SumBold(B1:B50)</code>

    Note: Steve used <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve indentation. If you copy and paste text with pre tags directly into the Visual Basic Editor, line feeds are lost. That is the reason for using Word as intermediary.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sum cell if bold (Excel 2000)

    YOu don't have to do it in a macro, you can use it like a "normal" excel function:
    Create the function (do this once)
    Goto VB ( Alt-F11)
    Insert - module
    in the right hand code pane
    Copy the macro function I wrote
    Exit VB (alt-q)

    Using the function
    Then in A50 enter the formula:
    <pre>=SumBold(B1:B50)</pre>


    This formula can be used as often as desired, with various ranges.
    Steve

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum cell if bold (Excel 2000)

    Hans, I did exactly as you requested (using word) and I got this error message: "Ambiguous name detected: SumBold.". Can you help. Thank you.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum cell if bold (Excel 2000)

    Did you happen to paste the code twice? Or did you name the module SumBold as well?

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum cell if bold (Excel 2000)

    As usual, you are awesome and brilliant. I had both VB and a macro with the same info. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •