Counting Cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Missile - 20 Jan 2006 07:37 GMT

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

Reply to this Message

Bob Phillips - 20 Jan 2006 08:33 GMT

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

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> 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

Reply to this Message

Missile - 20 Jan 2006 09:17 GMT

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.

> See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution.
>
[quoted text clipped - 5 lines]
> >
> > Thanks

Reply to this Message

Gary''s Student - 20 Jan 2006 09:46 GMT

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

Signature

Gary''s Student

> 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
[quoted text clipped - 9 lines]
> > >
> > > Thanks

Reply to this Message

Missile - 20 Jan 2006 09:53 GMT

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.
[quoted text clipped - 12 lines]
> > > >
> > > > Thanks

Reply to this Message

Missile - 20 Jan 2006 11:47 GMT

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

> Ahh ok, thanks, will give it ago
>
[quoted text clipped - 14 lines]
> > > > >
> > > > > Thanks

Reply to this Message

Max - 20 Jan 2006 23:16 GMT

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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

Reply to this Message

Missile - 22 Jan 2006 20:41 GMT

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

> Here's a recent* sample file (~ "starter's kit") which contains an
> implementation of Bob Phillips' ColorIndex Function from his "Processing
[quoted text clipped - 20 lines]
> >
> > Thanks

Reply to this Message

Peo Sjoblom - 22 Jan 2006 21:16 GMT

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

Signature

Regards,

Peo Sjoblom

Portland, Oregon

> 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.
[quoted text clipped - 28 lines]
>> >
>> > Thanks

Reply to this Message

Max - 22 Jan 2006 22:55 GMT

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. ... "
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message