Counting Colored cells in a database

K

Kelly Lim

I would like 2 apologize to everyone who have help me on the post of
"Counting Cells" but im confused with everyone agreeing and disagreeing on
using this and that methods...which i don know where to make ammendments in
my code and formulas now....Thanks to u all first....

Could someone pls help me clearly on how should i do it?
My database contains some coloured cells.....with conditional
formatting.....and i would like to have maybe the calculation below
.......which will calculate how many E.g. columns in red
columns in orange
columns in yellow
and etc........

Its urgent and i would be so glad if anyone can help me step by step
and clearly on this? Im not really good in Excel....that's y im trying to
learn..... Thanks to all.....
 
P

Peo Sjoblom

If the column(s) has conditional formatting as in format>conditional
formatting then I think the best way would be to count based on the
conditions of the formatting, what are the conditions for the different
colours? Then use those conditions either in a countif or sumproduct
depending on how complicated the conditions are. Otherwise you can go here
to find CF functions

http://www.cpearson.com/excel/CFColors.htm

to install a function, if you want it to be connected with this particular
workbook, open the workbook with the colours, press Alt + F11, look in the
project pane to the left to make sure it is the right workbook, do
insert>module and paste the function code in there, press Alt + Q to close
the editor and save the workbook, thee refer to it as

=MyFunction()

If you want it to be available for all workbooks you can paste it into the
personal.xls (the drawback is that you have to refer to it as in

=Personal.xls!MyFunction()

or you can open a new workbook, press Alt + F11 like in the first example,
then save it as MyFunction.xla (*.xla), put it in the xlstart and check it
under tools>add-ins, then you can refer to it as

=MyFunction()

w/o the reference to the personal.xls from any workbook. That is usually
what I do
 
K

Kelly Lim

Hi Peo,
pasting the function code to "Insert > Module" meaning? the code from the
URL that u gave me?? copy paste all in there....??

and where would like write this =MyFunction() to?

Sorry.....because im not good in it...
 
D

Dave Peterson

I think I'd try to mimic the conditional formatting formula in the formula that
would count the cells. I've found that working with conditional formatting
inside a userdefined function much more difficult than just mimicking those
rules.

For instance, if the orange colored cells are the ones that have "Apples" in the
cell, then:
=countif(a1:c9,"apples")
could be enough. (Yeah, a totally simple example!)
 
K

Kelly Lim

What do u mean by mimic the conditional formatting formula in the formula
that would count the cells?? ....on the coloured cells itself...already
contains conditional format....so i cannot insert more on to them.....

So now at the bottom of the columns...i would like them to add up how many
cells are in red colour, yellow colour, blue colour and etc are there in this
column??......

Pls help...thanks
 
P

Peo Sjoblom

What Dave means is why is a cell red or blue etc? Then use the same
criteria.
It's hard to explain any further, did you do the conditional formatting,
then what is the condition the formatting is based on?

--
Regards,

Peo Sjoblom

(No private emails please)
 
B

Biff

It's hard to explain any further, did you do the conditional formatting

No, I did about 2 wks ago!

And a week after that I did the formulas to count the cells based on the cf
criteria (due dates) but this poster insists on using a UDF.

So......

Biff
 
M

Max

Biff said:
No, I did about 2 wks ago!

And a week after that I did the formulas to count the cells based on the cf
criteria (due dates) but this poster insists on using a UDF.

So......
Click to expand...

And the saga continues, Biff <bg>. Think the OP has posted 2 further
responses to you in the earlier thread. It seems the OP has a combination
of both CF and non CF colored cells (inclusive "no fill color" cells) that
s/he needs to work with. I've posted my 2 cents worth over there ..
 
Top