detecting a colour?

[legepe]

=SUMA(CONTAR.SI(B14:AC14,{1;2;3;4}))

Using the above formula is it possible to detect a colour rather than a number and how can this be done?

Many thanks

legepe

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution

[legepe]

Thanks for that ive tried modifying the following formula but it keeps displaying NAME

=SUMARPRODUCTO(--(ColorIndex(B16:AC16)=3))

Is there anything obviously wrong with it?

[Bob Phillips]

Thanks for that ive tried modifying the following formula but it keeps displaying NAME

=SUMARPRODUCTO(--(ColorIndex(B16:AC16)=3))

Is there anything obviously wrong with it?

Only that you probably haven't copied the Colorindex func tion from that page into your workbook.

[legepe]

I copied the Colorindex function and I have got it to work "kind of"
If I colour a cell in red it does not automatically increment in the cell with the formula, but if I then drag that cell to any other cell within the range it will increment
So it is kind of working but not 100%
Also, I tried with red text but it just displays VALUE
=SUMAPRODUCTO(--(ColorIndex(B16:AC16,TRUE)=3))

[Bob Phillips]

I copied the Colorindex function and I have got it to work "kind of"
If I colour a cell in red it does not automatically increment in the cell with the formula, but if I then drag that cell to any other cell within the range it will increment
So it is kind of working but not 100%
Also, I tried with red text but it just displays VALUE
=SUMAPRODUCTO(--(ColorIndex(B16:AC16,TRUE)=3))

Reda the constraints in the article.

[legepe]

"What I do in applications that use this technique is to create a button(s) to set the colour(s), and within the code attached to the button(s), I do a manual sheet calculate."

I do not understand what he means with this??

[Bob Phillips]

"What I do in applications that use this technique is to create a button(s) to set the colour(s), and within the code attached to the button(s), I do a manual sheet calculate."

I do not understand what he means with this??

He means that setting a colour will not trigger a worksheet or range recalculation, so instead of using the colour controls on the Standard toolbar, he adds his own button that simulate these, but also issues a worksheet calculate command. That way, an update to the formula is forced when a colour is set.

[legepe]

Is there not another way to update a macro command?

[Bob Phillips]

Is there not another way to update a macro command?

You'll have to expolain what that question means.