Hi Tom!
Try a visit to Chip Pearson's site for this issue:
Chip Pearson:
http://www.cpearson.com/excel/colors.htm
Here's an extract that covers what you want:
Start Extract>>
The following function will return the sum of cells in a range that
have either an Interior (background) or Font of a specified color.
InRange is the range of cells to examine, WhatColorIndex is the
ColorIndex value to count, and OfText indicates whether to return the
ColorIndex of the Font (if True) or the Interior (if False).
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
End Function
You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)
<<End Extract
You'll find that bookmarking Chip's Index page is well worth it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
