Counting Cells

[Missile]

I want to be able to count the number of cells that have coloured text from a
range of cells. Is their a way to do this, I tried "countif" but was unable
to get it working.

Thanks

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Missile]

Thanks for the reply, unfortunatly when I try one of the solutions all I get
is an error. i don't know why but I am unable to get it to work. Even when I
tried to find the colorindex, that would not work.

 

[Gary''s Student]

Before the formulae in the webpage will work you need to enter the VBA code
near the bottom of the webpage.

 

[Missile]

Ahh ok, thanks, will give it ago

Before the formulae in the webpage will work you need to enter the VBA code
near the bottom of the webpage.

 

[Missile]

Just spent the past hour or so looking at the VBA code and realised I have
absolutly no idea what it all means and how to apply anything I need. Looks
like I will just have to used old fashion pen and paper, lol

Thanks

 

[Max]

Here's a recent* sample file (~ "starter's kit") which contains an
implementation of Bob Phillips' ColorIndex Function from his "Processing
Coloured Cells" page at:
http://www.xldynamic.com/source/xld.ColourCounter.html
and some examples on how to use the UDF in Excel
*given in response to another query

Link to the sample file (construct details inside):
http://www.savefile.com/files/3348428
CountCellsByFillColor_Using_BobPhillips_ColorIndex_UDF.xls

Hope the sample helps to get you started ..

 

[Missile]

Thanks all for you replies, low as my brain is I finally got it to work.
Only problem is, for it to work i have to save/close and reopen the file. It
dosent seem to update on the fly.

Missile

 

[Peo Sjoblom]

tools>options>calculation set to automatic, if you are using a UDF that is
non volatile press F9 to calculate

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Max]

Thanks, Peo !

Missile: Yes, forcing recalc is one of 2 constraints highlighted by Bob
under the section "Constraints" in his "Processing Coloured Cells" page
(link provided earlier, part extract below). The other is that the UDF
(presently) does not count colours set by conditional formatting.

" .. The first shortcoming is that changing a cell's colour or the text
colour does not trigger the Excel calculate event. This is not a shortcoming
of the UDF, but of Excel's calculation event, but what this means is that
any formula that uses the Colorindex UDF to calculate a cell's colour will
not update should that cell's colour change. ... "