Check if range has Subtotal or exclude subtotal-CodePudding

I have simple code that sums a range of number based on its font color. But it also sums the subtotals in that range. How can I skip a cell if its a subtotal or in some other way exclude to sum the subtotals?

Here is my code:

Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
Dim xTotal As Double
xTotal = 0
For Each rng In pRange1
    If rng.Font.Color = pRange2.Font.Color Then
        xTotal = xTotal   rng.Value
    End If
Next
SumByColor = xTotal
End Function

CodePudding user response：

assuming subtotals are either use subtotal formula or sum (and nothing you want to include does) this works:

Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
    'Update by Extendoffice
    Application.Volatile
    Dim rng As Range
    Dim xTotal As Double
    xTotal = 0
    For Each rng In pRange1
        If InStr(1, LCase(rng.Formula), "sum") = 0 And InStr(1, LCase(rng.Formula), "subtotal") = 0 Then
            If rng.Font.Color = pRange2.Font.Color Then
                xTotal = xTotal   rng.Value
            End If
        End If
    Next
    SumByColor = xTotal
End Function

if there are sums you want included and the subtotals do use the subtotal formula then remove the first instr condition that I added

credit to @Foxfire And Burns And Burns who got there first though