Fill values for formula

[gardener]

I am trying to write a formula that will do the following: if a cell i
fill in with color, do not sum values, otherwise sum values. However
I do not know what the fill variable is to put in the formula. Hop
this make sense. Please help.

Thank You

 

[Norman Harker]

Hi gardener!

See:

Chip Pearson:
http://www.cpearson.com/excel/colors.htm

Watch out for colors that result from conditional formatting as they
are treated differently from "manually" imposed colors.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
news:[email protected]...

 

[Jazzer]

Hi,

Function SumIfFilled(rArea As Range) As Double
Dim r As Range

Application.Volatile

For Each r In rArea
If r.Interior.ColorIndex = xlNone Then
SumIfFilled = SumIfFilled + r.Value
End If
Next
End Function


This should do the trick. Remember that allthough this is marked as
volatile, the changing of fill color doesn't automatically recalculate
the value of this function. You should either change some value on the
worksheet or press F9 to recalculate this function.

- Asser