Colored Cells & Cell Formulas

[MoltenPoo]

Is there any way to utlized cell formulas to determine cell or text color? For example, if I have data populating A1:A5 and A1:A2 have a fill color of yellow and A3:A5 have a fill color of blue, can I use some formula with a "SUMIF" command to add by color?

 

[Ivan F Moala]

These are all good options.
Also search the board, this has been covered
with lots of examples and site references.

 

[Cosmos75]

Will the UDF Colorsumif work for cells that are colored using conditional formatting? I've seen a few posts like this and they all give answers but warns that it doesn't work for conditionally formatted cell. Why is this the case?

 

[NateO]

Conditional formatting doesn't operate the same, to put it mildly. I did post a couple of solutions (leveraging off of others, credit was posted respectively), but they only work on small ranges. You're better off testing the data for the same criteria that the was used to apply the format. To the matter at hand. Here's a udf to test, haven't tried Nimrod's, but if you want the recalc. to work properly, you need to mention that the function is volatile (vba style):

Function SumByColor(InputRange As Range, ColorRange As Range) As Double
Dim cl As Range, TempSum As Double, ColorIndex As Integer
Application.Volatile
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempSum = 0
On Error Resume Next
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex _
Then TempSum = TempSum + cl.Value
Next cl
On Error GoTo 0
Set cl = Nothing
SumByColor = TempSum
End Function

Now use =sumbycolor(B1:F18,D1) where the first parameter is the test range and the second param is a cell that holds the format. This is leveraged from http://www.erlandsendata.no/english/functions/adding/sumbycolor.htm, but I've elected to add the volatility.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-11 11:51