sbSumMyFormat

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 a cell color does not trigger a recalculation of dependent cells.
You would need to invoke 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 recalc or an event procedure which starts this whenever a different cell is being selected, for example.
Please read my Disclaimer.
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).
'Reverse("moc.LiborPlus.www") PB V0.21 10-Jul-2011
Dim v
​
For Each v In r
    If v.NumberFormat = Application.Caller.NumberFormat Then
        sbSumMyFormat = sbSumMyFormat + v
    End If
Next v
​
End Function
Another example on how to count colored cells: [Cells B1:B10 are named ColoredRange here]
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).
'Reverse("moc.LiborPlus.www") PB V0.11 10-Jul-2011
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
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
sbSumMyFormat.xlsm
27KB
Binary
sbSumMyFormat.xlsm

sbSumLast

sbSWV

Last modified 2yr ago

Copy link