VBA defined function problem sumbycolor

[Brian Matlack]

Hi!
I recently got this bit of code from the forum here and it works great
as long as I format the cells myself but there are two problems
1. It does not work if I use conditional formatting.
2. I have to hit F9 to get it to recalculate the formula if I format
another cell in the range.
Can I get this to work on conditional formatting and auto update?

<Start code>
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim rng As Range
Dim OK As Boolean

Application.Volatile True
For Each rng In InRange.Cells
If OfText = True Then
OK = (rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(rng.Value) Then
SumByColor = SumByColor + rng.Value
End If
Next rng

End Function

Thanks for any help!!

 

[Bob Phillips]

1. Very difficult. See http://www.xldynamic.com/source/xld.CFConditions.html

2. No. Cell colouring does not trigger any event that will force a
recalculation, so you have to force it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brian Matlack" <[email protected]>
wrote in message
news:[email protected]...

 

[Brian Matlack]

1. Very difficult. See
http://www.xldynamic.com/source/xld.CFConditions.html

2. No. Cell colouring does not trigger any event that will force a
recalculation, so you have to force it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brian Matlack"
<[email protected]>
wrote in message

Thanks for the help Bob!