Count by Cell Colour - Excel VBA Function 

Thank you so much for this sir. I wish I found your RU-vid channel 1 day ago - it would've saved me a lot of time! Be blessed

I find it amazing that Microsoft do not have a simple function for this exercise. However, after trying a few other channels with this problem, this was the easiest by far. Worked like a dream first time. Many thanks.

Thank you! I tried four other custom functions and this is the only one to do the trick. :)

Only a clever person can explain with simplicity such cumbersome task. Nice video, clear explanation. Both thumbs up.

Thank You very much. You really helped me with my project. Congratulations for such a great lesson!

Thanks so much for this - I wish I found your RU-vid channel 3 hours ago - it would've saved me a lot of angst!

Thank you so much. It works.

I kept getting an error, then decided to save my progress, and excel said I didn't have macros enabled... So I told it to enable them and it works! If anyone keeps getting an error that says you haven't put brackets in the right place, this might be your problem too.

Thanks Mr. Alan

thank you a lot

thank you

Thank You

Hi Guys, thanks for all this beautiful work. I just want to know, how would the "custom function" which recognise font colour look like?

This macro did what I wanted. But I am using a yearly calendar and I have it working where I shade a date with a color and it adds up the vacation/sick/personal time for me. BUT if I need to change anything it does't dynamically update, same if I just change the color using the color selector in the Home ribbon and not copying the target cell from the macro. Is there a way to make this dynamically checking for changes in the cell colors?

it's great thank you very much for sharing this. When changing the fill color of the ranged cells, returned counted value does not update the number of coloured cells unless you perform F2 + Enter, would that be possible to have it update the number of colored cells automatically when introducing these additional colored cells? thank you very much, highly appreciated.

Hello my brother Alan. If we change a color of some cell, the result of that color stay the same, though I refreshed other cells by changing it value also the result of that color stay the same, so I tried to refresh the formula which counts that colour then it gave me the new right result. well, i think we have to write the code in some place "like VB" onto "changing part".... but i can't find it in VBA of Excel

Thanks for making this video. Is there any way we can do same thing on online version.

Please do you know how to formulate the following condition in excel; when you have two cells one with high and the other with low value and one of the cell is colored green. so you want to return a value of '1' if the lowest value amoung the two cell is green color and '0' is the highest value amoung the cell is green. is there a condition or formula for the statement in excel

Hello, Not sure if i'll be able to explain this very well but here goes... I am trying to do this with cells that have conditional formatting which changes the colour of a cell depending on data in another cell. This video covers counting cells where the colour is manually inputed but is there a way of counting colours that are automatically changed due to conditional formatting rules? I input a date in one cell, the following cell will change colour when an date (e.g. =(E20+730) ) reaches within 45days (orange) and again (red) once the date has been passed... I'm hoping to create a dashboard on another page that can count when a box in a certain range has changed to these colours giving the option to quickly glance and see that no dates have expired passed a predetermined amount of days... Does that make sense?... Any ideas? Any help would be appreciated!... Thanks for your time! : )

Hello Computergaga, I have tried it and it won't work on the colour that's done with Conditional Formatting. Any solutions? Thanks for your help.

How to do the colour count dynamically with conditional formatting in place

Does it work with conditional formatting rule in place

hi, i've copied your module and inputted into my excel but my end result comes with '#NAME?' what am i doing wrong ?

I made the same procedure a saw in your excel but did not get that option "COUNTIFCOLOUR" Let me know where I am making mistake?

Sadly, it doesn't recognizes Conditional Formatting color fill...

How do we make it work automatically when the ccolor changes

Hi in my excel function is not showing using Windows 7

totally smashed by problem thanks very much. However how do i add this to 365 columns in one row so i can monitor how many coloured tabs are in each of the days columns lol

So I tried this but the count keeps returning "0". Anything I'm doing wrong?

HOW TO SOLVE THE VALUE UPDATE PROBLEM AFTER INSERTING A NEW PAINTED CELL: COMO RESOLVER O PROBLEMA DA ATUALIZAÇÃO DO VALOR APÓS INSERIR UMA NOVA CÉLULA PINTADA: Galera, aqui é o Estagivídeos Depois que você conseguir calcular o número de linhas preenchidas com cor, você deve gravar uma macro para atualizar esse valor toda vez que ocorrer alguma modificação na sua célula. 1° PASSO: Identifique a célula que está o resultado do número de células preenchidas com cor, ou seja, a célula que você colocou o resultado da macro. 2° PASSO: Ative essa célula em que está contido o resultado de sua macro, usando o seguinte código : Nesse exemplo o resultado da minha macro está na célula G10 cel_ativa = Range("G10").Activate 3° PASSO : Grave a macro. Após aperta o botão "Gravar Macro" lá na aba desenvolvedor, execute as seguintes ações > Der dois clicks na célula em que está o resultado de sua macro (nesse exemplo é célula G10). >Logo após, vai aparecer a formula contida nessa célula (aquela formula que foi colocada la no módulo com nome function). Após o cursos ficar piscando dentro da formula, pressione enter. Feito isso, pode parar de gravar a macro. O que você acabou de fazer foi atualizar o valor da célula que você pressionou ENTER. A macro vai ficar parecida com essa Range("G10").Select ActiveCell.FormulaR1C1 = "=IFERROR(contarcores(RC[-1],R2C1:ult_cor),"""")" Range("G11").Select Você vai precisar recortar essa parte ActiveCell.FormulaR1C1 = "=IFERROR(contarcores(RC[-1],R2C1:ult_cor),"""")" ela simplesmente está dando dois clicks e enter na célula que você ativou anteriormente lá no 2° PASSO, ou seja, essa linha de código atualiza o valor da célula ativada. Agora, você pode usar essa linha de código dentro do evento change por exemplo, pra toda vez que uma célula expecifica for alterada, ele activar sua célula que contem o valor da macro, dar dois cliks nela e depois enter, assim vai atualizar automatimente. Qualquer dúvida, mandem mensagem lá no meu canal que eu posso gravar um vídeo. SE INCREVAM POR GENTILEZA :)