Nick,

This post taught me something I never realized. I'm so used to thinking
of user-defined-functions as needing to be pure functions - limited to
returning a value which can be displayed in a cell and with no
side-effects involving formatting, etc. - that I had implicitly assumed
that UDFs couldn't even involve formatting. Hence my somewhat
round-about suggestion of linking my version of the colorsum function
to the calculate event. But when you wrote "To use enter something like
=ColorSum(A1:A20,10) into a cell." I realized that the only
restriction was on the "output" of the UDF but that the "input" can
involve virtually anything. I'm not quite sure where I would use that
fact, but I'm sure that sooner or later it would come in handy. Thanks.

-John



NickH wrote:
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