Abstract
If you have an area filled with numbers of different currencies (i.e. different number formats) and if you need to calculate totals for all of these:
Please note that changing the number format or the color of a cell does not trigger its recalculation (nor of dependent cells).
You would need to invoke a recalculation manually with CTRL + ALT + F9, for example.
Other reasonable options to ensure an up-to-date calculation of dependent cells are a manual button which starts the recalculation, or an event procedure which starts this whenever a different cell is being selected, for example.
Appendix sbSumMyFormat Code
Please read my Disclaimer.
Option Explicit
Function sbSumMyFormat(r As Range)
'Sums up all values in r which have the same number format
'as calling cell (where this function is called from).
'Please keep in mind that this function is NOT automatically
'updated if an input cell format changes because that does
'not trigger a recalculation event! To ensure an up-to-date
'calculation you need to do this yourself
'(with CTRL + ALT + F9, for example).
'Source (EN): http://www.sulprobil.com/sbsummyformat_en/
'Source (DE): http://www.bplumhoff.de/sbsummyformat_de/
'(C) (P) by Bernd Plumhoff 10-Jul-2011 PB V0.21
Dim v
For Each v In r
If v.NumberFormat = Application.Caller.NumberFormat Then
sbSumMyFormat = sbSumMyFormat + v
End If
Next v
End Function
Additional Example: Count My Color
Another example on how to count colored cells: [Cells B1:B10 are named ColoredRange here]
Appendix sbCountMyColor Code
Please read my Disclaimer.
Option Explicit
Function sbCountMyColor(r As Range)
'Counts all cells of r which have the same color as calling
'cell (where this function is called from).
'Please keep in mind that this function is NOT automatically
'updated if an input cell format changes because that does
'not trigger a recalculation event! To ensure an up-to-date
'calculation you need to do this yourself
'(with CTRL + ALT + F9, for example).
'Source (EN): http://www.sulprobil.com/sbsummyformat_en/
'Source (DE): http://www.bplumhoff.de/sbsummyformat_de/
'(C) (P) by Bernd Plumhoff 10-Jul-2011 PB V0.11
Dim v
For Each v In r
If v.Interior.Color = Application.Caller.Interior.Color Then
sbCountMyColor = sbCountMyColor + 1
End If
Next v
End Function
This Sub would need to be inserted into the code section of the related worksheet:
Please read my Disclaimer.
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim state As SystemState
Set state = New SystemState 'Please look into class module
'SystemState: Events are being disabled here
Call RecalculateColoredRangeDependents
End Sub
Sub RecalculateColoredRangeDependents()
Range("ColoredRange").DirectDependents.Calculate
End Sub