Colour Count
 

Having trouble with the above, although the two links posted previously were
helpful in how i should do it [cpearson and xldynamix] but I have been unable
to count cells with a specific colour in them.

I think it maybe my inputting of the VBA code as I am not sure I am doing
this correctly. I have just cut and pasted the code from this site into VBA,
but do not know enough about it to ensure this is right. After inputting the
formula code, when I use it, it is not reconised and returns a NAME error.

Can anyone tell me what I am doing wrong and how to correct it?

http://xldynamic.com/source/xld.Colo...r.html#sorting

Colour Count
 

Open Excel
Press Alt/F11 together to open VBA
Select Insert | Module from the Menu
Copy all the code from the code samples into the newly created module
(from "ColorIndex Function" down to "End of ColorIndex Function")

Use the examples to test that the function works correctly

=SUMPRODUCT(--(ColorIndex(A1:A100)=3))
counts all red cells (background color) within the range A1:A100

or
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))
counts all cells with red text within the range A1:A100

To get the colorindex of a specific cell, simply use
=ColorIndex(A2)


Regards

Trevor


"shakey1181" wrote in message
...
Having trouble with the above, although the two links posted previously
were
helpful in how i should do it [cpearson and xldynamix] but I have been
unable
to count cells with a specific colour in them.

I think it maybe my inputting of the VBA code as I am not sure I am doing
this correctly. I have just cut and pasted the code from this site into
VBA,
but do not know enough about it to ensure this is right. After inputting
the
formula code, when I use it, it is not reconised and returns a NAME error.

Can anyone tell me what I am doing wrong and how to correct it?

http://xldynamic.com/source/xld.Colo...r.html#sorting

Colour Count
 

This works ok, however it doesn't recount when I change any of the cells
colour, is there a seperate thing I have to do?

"Trevor Shuttleworth" wrote:

Open Excel
Press Alt/F11 together to open VBA
Select Insert | Module from the Menu
Copy all the code from the code samples into the newly created module
(from "ColorIndex Function" down to "End of ColorIndex Function")

Use the examples to test that the function works correctly

=SUMPRODUCT(--(ColorIndex(A1:A100)=3))
counts all red cells (background color) within the range A1:A100

or
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))
counts all cells with red text within the range A1:A100

To get the colorindex of a specific cell, simply use
=ColorIndex(A2)


Regards

Trevor


"shakey1181" wrote in message
...
Having trouble with the above, although the two links posted previously
were
helpful in how i should do it [cpearson and xldynamix] but I have been
unable
to count cells with a specific colour in them.

I think it maybe my inputting of the VBA code as I am not sure I am doing
this correctly. I have just cut and pasted the code from this site into
VBA,
but do not know enough about it to ensure this is right. After inputting
the
formula code, when I use it, it is not reconised and returns a NAME error.

Can anyone tell me what I am doing wrong and how to correct it?

http://xldynamic.com/source/xld.Colo...r.html#sorting