Conditional Formulas

[Don]

I am trying to write a formula that will not calculate
cells in a range if the cell is shaded. Is this possible?

Thanks

 

[JohnI in Brisbane]

Don,

Can be done as a UDF-


Function SumUnshaded(rngData As Range) As Double
Dim rngCell As Range
SumUnshaded = 0
For Each rngCell In rngData
If rngCell.Interior.ColorIndex = xlNone Then
SumUnshaded = SumUnshaded + rngCell.Value
End If
Next

End Function

regards,

JohnI

 

[JMay]

Johnl:
Thanks for the code.
I modified as follows to have my procedure Sum only the Shaded Cells in the
Range as follows:

Function Sumshaded(rngData As Range) As Double
Dim rngCell As Range
Sumshaded = 0
For Each rngCell In rngData
If Not rngCell.Interior.ColorIndex = xlNone Then
Sumshaded = Sumshaded + rngCell.Value
End If
Next

End Function

What should be added to the above code to immediately
update the function as another cell in the range is instantly
"colored/shaded"?
Shading a cell doesn't qualify as a Sheet Change event I don't think.
TIA,,