VBA defined function problem sumbycolor

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!!

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" <Brian.Matlack.25t6gn_1144269602.9893@excelforum-nospam.com>
wrote in message
news:Brian.Matlack.25t6gn_1144269602.9893@excelforum-nospam.com...
>
> 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!!
>
>
> --
> Brian Matlack
> ------------------------------------------------------------------------
> Brian Matlack's Profile:
http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=530239
>

Originally Posted by 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" <Brian.Matlack.25t6gn_1144269602.9893@excelforum-nospam.com>
wrote in message
news:Brian.Matlack.25t6gn_1144269602.9893@excelforum-nospam.com...
>
> 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!!
>
>
> --
> Brian Matlack
> ------------------------------------------------------------------------
> Brian Matlack's Profile:
http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=530239
>

Thanks for the help Bob!