I think John's probably right, especially if you are already using
Excel's built-in Conditional Formatting.

However, should you decide to go the VBA route the following functions
may be of some help.

NB. These functions will NOT detect a cell interior colour that has
been set using conditional formatting. To do that you would need to
write code to interrogate the condtional format settings, in which case
you might as well write your own conditional formatting routine (yeah,
listen to John).

Still reading? Okay go here for some Conditional Format macro
suggestions...

http://www.mvps.org/dmcritchie/excel/condfmt.htm

Here's a function to do the summing...

Public Function ColorSum(mRng As Range, mColor As Integer) As Single
Dim mTot As Single
Dim c As Range


For Each c In mRng
If IsNumeric(c.Value) Then
If c.Interior.ColorIndex = mColor Then
mTot = mTot + c.Value
End If
End If
Next c

ColorSum = mTot

End Function

To use enter something like =ColorSum(A1:A20,10) into a cell.

And here's a function to return the interior colour index of a cell...

Public Function GetColorIndex(mCell As Range) As Integer
''' Quick check to find the interior color of a cell. _
If multiple cells selected only top left examined

GetColorIndex = mCell.Range("A1").Interior.ColorIndex
End Function

To use enter something like =GetColorIndex(A3) into a cell.


Hope you listened to John - I'm just killing time. ?;^)

NickH