UDF help

[Sylvan]

Kind of tricky to explain. I've written a UDF to sum bolded values until the next cell with a fill color is encountered. It works fine until it's copied down as part of a range and then I get a #VALUE! error. I'll then enter some values and it starts working. But then when I run a macro to paste a range below the UDF it gives me the #VALUE! error again.

Here's the code -- any ideas? Thanks in advance

Function ROOMSUM(MyRange As Range) As Double
'Sums bolded values down until the next colored cell

Dim rCell As Range

For Each rCell In MyRange
If rCell.Interior.ColorIndex <> xlNone Then Exit Function
If rCell.Font.Bold = True Then
ROOMSUM = ROOMSUM + rCell.Value
End If
Next rCell

End Function

 

[shg]

Just following the data in your example.

There are lots of functions that can do conditional summing. I'd use any of those before depending on cell formatting as a condition.

In R12 you could put the formula

=IFERROR(MATCH("New Room", A13:A1000, 0), 1000)

And in N12,

=SUMPRODUCT(N13:INDEX(N:N, ROW() + $R12) * (B13:INDEX(B:B, ROW() + $R12) = "New Piece"))

 

[Sylvan]

Thanks