Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
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 |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
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 |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
I think you need to use a User Defined Function to do that... Thank you for the detailed response which will be a great help to me, Steve. |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
Hi Steve,
You're welcome. Hope it works out OK. Ken Johnson |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
You're welcome. Hope it works out OK. 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. |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
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 |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
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. This will work nicely since the worksheet involves summing less than 50 items. Thank you for this refinement. Steve |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
SUM if cell is Bold
Hi Steve, You're welcome. Thanks for the feedback. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bold text of referenced cell show in formula cell | Excel Worksheet Functions | |||
Alphabetically list of last names: BOLD, not bold | Excel Discussion (Misc queries) | |||
Alphabetically list of names BOLD and NOT bold | Excel Discussion (Misc queries) | |||
Join bold and non-bold text in one cell | Excel Discussion (Misc queries) | |||
How can i test a cell for bold style in a cell (Excel 2003)? | Excel Worksheet Functions |