Chris,
You can do this only with code:
Function SumExceptColor(InputRange As Range, ExcludeCI As Integer, _
OfText As Boolean) As Double
Dim Rng As Range
Dim Total As Double
For Each Rng In InputRange.Cells
If IsNumeric(Rng.Value) = True Then
If OfText = True Then
If Rng.Font.ColorIndex <> ExcludeCI Then
Total = Total + Rng.Value2
End If
Else
If Rng.Interior.ColorIndex <> ExcludeCI Then
Total = Total + Rng.Value2
End If
End If
End If
Next Rng
SumExceptColor = Total
End Function
In this function, InputRange is the range to sum. ExcludeCI is the
ColorIndex to exclude from the summation (Red = 3), OfText indicates whether
to look at the color of text (if OfText = True) or the background (if OfText
= False) of each cell.
You can call this function with
=SumExceptColor(A1:A10,3,TRUE)
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)
