=countbycolor anomalies
 

I am using the =countbycolor function provided by Chip Pearson
(http://www.cpearson.com/excel/colors.htm) to count cells with a certain
color font and am experiencing the following two anomalies:

- Formulas do not automatically update, automatic calculations is ON in all
worksheets, except when deleting cells. Adding cells or changing cells, and
selecting desired font, requires manual update.

- Clearing or deleting contents of cell/row doesnt change the formula
results, even after manually updating. When the cell/row is deleted, the
formulas results change properly after manual update.

Any assistance you could provide would be greatly appreciated. Thanks much!

=countbycolor anomalies
 

Colour changes do not trigger events so you will not get an automatic
recalculation.

You could add

Application.Volatile

at the start of the code, and force recalculation with Alt-F9, but you won't
get automatic recalculation.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Bilbert" wrote in message
...
I am using the =countbycolor function provided by Chip Pearson
(http://www.cpearson.com/excel/colors.htm) to count cells with a certain
color font and am experiencing the following two anomalies:

- Formulas do not automatically update, automatic calculations is ON in
all
worksheets, except when deleting cells. Adding cells or changing cells,
and
selecting desired font, requires manual update.

- Clearing or deleting contents of cell/row doesn't change the formula
results, even after manually updating. When the cell/row is deleted, the
formulas results change properly after manual update.

Any assistance you could provide would be greatly appreciated. Thanks
much!

=countbycolor anomalies
 

That is why you should not base calculations on cell formats.

Biff

"Bilbert" wrote in message
...
I am using the =countbycolor function provided by Chip Pearson
(http://www.cpearson.com/excel/colors.htm) to count cells with a certain
color font and am experiencing the following two anomalies:

- Formulas do not automatically update, automatic calculations is ON in
all
worksheets, except when deleting cells. Adding cells or changing cells,
and
selecting desired font, requires manual update.

- Clearing or deleting contents of cell/row doesn't change the formula
results, even after manually updating. When the cell/row is deleted, the
formulas results change properly after manual update.

Any assistance you could provide would be greatly appreciated. Thanks
much!

=countbycolor anomalies
 

Bob - Not being too familiar with the code, have I placed it in the correct
place? If so, it doesn;t appear to be working. Thank much!

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Application.Volatile

Dim Rng As Range
Application.Volatile True

"Bob Phillips" wrote:

Colour changes do not trigger events so you will not get an automatic
recalculation.

You could add

Application.Volatile

at the start of the code, and force recalculation with Alt-F9, but you won't
get automatic recalculation.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Bilbert" wrote in message
...
I am using the =countbycolor function provided by Chip Pearson
(http://www.cpearson.com/excel/colors.htm) to count cells with a certain
color font and am experiencing the following two anomalies:

- Formulas do not automatically update, automatic calculations is ON in
all
worksheets, except when deleting cells. Adding cells or changing cells,
and
selecting desired font, requires manual update.

- Clearing or deleting contents of cell/row doesn't change the formula
results, even after manually updating. When the cell/row is deleted, the
formulas results change properly after manual update.

Any assistance you could provide would be greatly appreciated. Thanks
much!