Is it possible to count coloured cells??

[optimus_1]

Morning all

I was wondering if it is possible to count the number of (different on occasions) coloured cells within a spreadsheet.

ie... Our company has a spreadsheet with a calendar and operatives colour cells 'red' if on annual leave, 'yellow' if on Training etc..... I was wondering if it is possible to summarize these colours to calculate the total number of days people have had off over time.

Regards
Nathan

 

[chergh]

Take a look at: http://www.cpearson.com/excel/colors.aspx

 

[optimus_1]

Hi thanks for the link.

I have looked through it and think i understand that the query

eg:
=COUNTCOLOR(A1:A10,3,FALSE)
may be what i am after as all i have is a straight line of different colours and wish to sum them, however when trying this calculation the spreadsheet doesn't seem to like it.

I have never had any involvement with the counting of colours, appears i am doing something wrong!!.

Thanks

 

[chergh]

Have you copied the countcolor function into a module in the vb editor?

 

[Brianwarnock]

I would have thought that the easiest to implement was

=SUMPRODUCT(--(COLORINDEXOFRANGE(B11:B17,FALSE,1)=COLORINDEXOFONECELL(H7,FALSE,1)))

Type of formula as the color index can be selected from a group of cells with the answers adjacent.

Brian

PS remember to download all of the module as per the script.

 

[optimus_1]

thanks for the help

I tried to write it in the VB editor and then writing the =countcolor function in the cell but i am out of my depth of knowledge on excel. I would really like to know what it is that i am doing wrong (probably lots!). I have attached a sample form of the sort of thing i am after.

Thankyou
Nathan

 

[chergh]

You needed to download the entire module as Brian advised, and not just the function. This is because the function can refer to other subs and functions.

Anyway the attached version should work.

 

[optimus_1]

Thankyou very much for that,
bit mindblowing for someone of my level but thanks for this, it has helped no end I am very grateful.

I can see how it is currently set up for colours Red, Blue and green. Is it easy enough to add other colours as and when they are needed?. Do i have to amend the vb code?.

Thanks again
Nathan

 

[optimus_1]

Think i have understood this a little better now. The colours are listed further down the vb code so is it just a case of changing the 'colour no' in the function.

ie.. =COUNTCOLOR(A5:I5,6,FALSE) - yellow
=COUNTCOLOR(A5:I5,8,FALSE) - Turquoise etc...

Thanks