Function SumColor(TestRange As Range, SumRange As Range, _
    ColorIndex As Long, Optional OfText As Boolean = False) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SumColor
' This function returns the sum of the values in SumRange where
' the corresponding cell in TestRange has a ColorIndex (of the
' Font is OfText is True, or of the Interior is OfText is omitted
' or False) equal to the specified ColorIndex. TestRange and
' SumRange may refer to the same range. An xlErrRef (#REF) error
' is returned if either TestRange or SumRange has more than one
' area or if TestRange and SumRange have differing number of
' either rows or columns. An xlErrValue (#VALUE) error is
' returned if ColorIndex is not a valid ColorIndex value.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim D As Double
Dim N As Long

Application.Volatile True
If (TestRange.Areas.Count >  1 ) Or _
    (SumRange.Areas.Count >  1 ) Or _
    (TestRange.Rows.Count <> SumRange.Rows.Count) Or _
    (TestRange.Columns.Count <> SumRange.Columns.Count) Then
    SumColor = CVErr(xlErrRef)
    Exit Function
End If
    
If IsValidColorIndex(ColorIndex:=ColorIndex) = False Then
    SumColor = CVErr(xlErrValue)
    Exit Function
End If
    
For N =  1  To TestRange.Cells.Count
    With TestRange.Cells(N)
    If OfText = True Then
        If .Font.ColorIndex = ColorIndex Then
            If IsNumeric(.Value) = True Then
                D = D + .Value
            End If
        End If
    Else
        If .Interior.ColorIndex = ColorIndex Then
            If IsNumeric(.Value) = True Then
                D = D + .Value
            End If
        End If
    End If
    End With
Next N
            
SumColor = D

End Function