Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|