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.
1
Function sbSumMyFormat(r As Range)
2
'Sums up all values in r which have the same number format
3
'as calling cell (where this function is called from).
4
'Please keep in mind that this function is NOT automatically
5
'updated if an input cell format changes because that does
6
'not trigger a recalculation event! To ensure an up-to-date
7
'calculation you need to do this yourself
8
'(with CTRL + ALT + F9, for example).
9
'Reverse("moc.LiborPlus.www") PB V0.21 10-Jul-2011
10
Dim v
11
​
12
For Each v In r
13
    If v.NumberFormat = Application.Caller.NumberFormat Then
14
        sbSumMyFormat = sbSumMyFormat + v
15
    End If
16
Next v
17
​
18
End Function
Copied!
Another example on how to count colored cells: [Cells B1:B10 are named ColoredRange here]
1
Function sbCountMyColor(r As Range)
2
'Counts all cells of r which have the same color as calling
3
'cell (where this function is called from).
4
'Please keep in mind that this function is NOT automatically
5
'updated if an input cell format changes because that does
6
'not trigger a recalculation event! To ensure an up-to-date
7
'calculation you need to do this yourself
8
'(with CTRL + ALT + F9, for example).
9
'Reverse("moc.LiborPlus.www") PB V0.11 10-Jul-2011
10
Dim v
11
​
12
For Each v In r
13
    If v.Interior.Color = Application.Caller.Interior.Color Then
14
        sbCountMyColor = sbCountMyColor + 1
15
    End If
16
Next v
17
​
18
End Function
Copied!
1
Option Explicit
2
​
3
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
4
    Dim state As SystemState
5
    Set state = New SystemState 'Please look into class module
6
                  'SystemState: Events are being disabled here
7
    Call RecalculateColoredRangeDependents
8
End Sub
9
​
10
Sub RecalculateColoredRangeDependents()
11
    Range("ColoredRange").DirectDependents.Calculate
12
End Sub
Copied!
sbSumMyFormat.xlsm
27KB
Binary
sbSumMyFormat.xlsm

sbSumLast

sbSWV

Last modified 1yr ago

Copy link