View Full Version : detecting a colour?
legepe
08-06-2006, 11:33 AM
=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
08-06-2006, 11:42 AM
See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution
legepe
08-06-2006, 11:52 AM
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
08-06-2006, 12:08 PM
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
08-06-2006, 12:46 PM
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
08-06-2006, 12:52 PM
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
08-06-2006, 01:05 PM
"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
08-06-2006, 03:03 PM
"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
08-06-2006, 03:29 PM
Is there not another way to update a macro command?
Bob Phillips
08-07-2006, 01:05 AM
Is there not another way to update a macro command?
You'll have to expolain what that question means.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.