Learn How to Count Cells by Color in Microsoft Excel

[@danpalmer4135]

This was so incredibly helpful. Literally, the first source I've found that was able to make this work for me. Thank you so much!

[@599CD]

You're very welcome!

[@ikimawsome]

This worked like a charm! Saved me hours of work! Thank you!

[@599CD]

You're welcome!

[@TeresaPegrim]

Thank you very much! This video was very helpful. It was my first time with VBA!

[@shrs.3448]

Thank you so much. This is exactly what I was looking for. First time trying VBA - a bit scary but your illustration is illuminating. thank you, thank you, thank you.

[@599CD]

Welcome welcome welcome. :)

[@amaregebrie6834]

You brighten my day..... Thanks!

[@599CD]

My pleasure!!

[@SwimminWitDaFishies]

I don't know what I just did, but I did it!!

[@mylenekeng1302]

Awesome!!! Your so clear in explaining how to do it...thanks for the help!!!!!!! 👍👍👍👍👍

[@599CD]

welcome

Thanks for sharing man! you are awesome because of doing this for everyone, keep being the great guy you are

[@599CD]

I will. Thanks. :)

[@paoloporchetti1563]

What a bliss! Thanks

[@599CD]

Okay sure

[@ReneB099]

Just what I was looking for, thank you 👍

[@599CD]

Welcome

[@sandyjohnson8549]

Would this work if I wanted to count all the colors in a row not just 1 color?

[@SwImMiNgRuNnEr]

This helped for sure, but when I changed one of the cells to another color, it didn't update the number automatically. Is there a way to fix that?

[@lawrence9713]

need this too

[@599CD]

You'd need to refresh the calculations. I'll add it to the list.

[@karidely]

I love this

[@599CD]

Thanks

[@fahadkooj7351]

I did it! Thanks A lot! :D

[@599CD]

welcome

[@rimisosaimoveis4392]

Hello. How and I use this formula with one more criteria? Example: I want to count the color cells when the range A1:A15 as a specific text. Thanks

[@leo_k1]

it works quite well but not with conditional formatting if you could fix this for me i,ll be so thankfull my guy

[@599CD]

K

[@stephanven]

Will this formula work if you need to count the colours from another sheet?

[@599CD]

Haven't tried it.

[@csilva19831]

Good morning Richard 🙏 ive used one of your youtube videos to ad a module/macro on a spreadsheet and it works great but when i decide to change the text in the target cell i get the #NAME? Error and even if i try to input the formula agian it keeps happening. What am i doing wrong?

[@599CD]

It's impossible for me to tell what you're doing wrong from here. I'd need to see everything. Just follow the video step by step.

[@DwikiOffice]

Public Function CountByColor(CellRange As Range, TargetCell As Range) Dim TargetColor As Long, Count As Long, C As Range TargetColor = TargetCell.Interior.ColorIndex For Each C In CellRange If C.Interior.ColorIndex = TargetColor Then Count = Count + 1 Next C CountByColor = Count End Function

[@599CD]

Thanks

[@GregBurcham-k1t]

Nice VBA, but if you use conditional formatting to get the colors it doesn't seem to work.

[@599CD]

Thanks

[@davidtanner4783]

YES!!!!!!

[@599CD]

Oui!!!!!!!!!!!

[@jerrydfowler3480]

Rick would something like this be possible in Access VBA?

[@599CD]

Not really... but in Access you really wouldn't change the color of individual cells like you can in Excel. Sure, you can use conditional formatting, but if you wanted to count those records, you could just do a count using whatever your formula you have for the CF in the first place.

[@jerrydfowler3480]

@@599CD as soon as you said that I'm like Duh lol of course you could.

[@vasilijeradonjic5439]

i have made the VBA code similar to this and it also works, but once i change the colour of a certain cell the count doesn't get updated automaticly. Is there any way to solve the problem?

[@599CD]

I'd have to see what you're doing to tell you for sure.

[@vasilijeradonjic5439]

@@599CD Function GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function i used the same criteria for range and coloured cells for colours i need as you did but every time i change a colour form the range to another one i have to enter the formula and press enter to refresh it. Is there any way to make excel to do this automaticlly?

[@vasilijeradonjic5439]

@@599CD it's the same with your code, i just tried it.

[@buzassyadam6468]

First of all thank you for this!!!! This is really helpful! My question is the same it is not refreshing whenever I change color.

[@aasavtamboli7919]

Sir When i tried using this CountbyColor Formula in Case of Conditional Formatting it Couldn't Count the Formatted Cells

[@599CD]

OK

[@annab4307]

@@599CD Sir any option how this can work for conditional formatting as well? Thanks.

[@Solukus-com]

Same issue here, did you find a solution?

[@goldenmask6689]

Thx

[@599CD]

Wlcm

[@sepidehnourian6404]

I did exactly what you showed but I get the #VALUE! error. what could be the issue?

[@599CD]

Well since I can't see your sheet there's no way I can tell you

[@95kcal]

I'm new to this and I followed all instructions but I couldn't get the function to come out.

[@599CD]

Sorry to hear that

[@marlegnnecastro8328]

This formula did not work for me. I have a feeling it’s because the cells I want to count are on a table with conditional formatting. I tried the formula on a separate sheet it worked like a dream. Anyone know how to fix this? TYSMIA

[@599CD]

That could be

[@roxyelling5809]

This did not work for me? How can I get help?

[@599CD]

You could post in my Forums: 599cd.com/Forums

[@jonstachelrodt]

This does not seem to work with Conditional Formatting in cells. Bummer :(

[@599CD]

Indeed!

[@walkeroglesby6071]

Damn I was getting frustrated because I thought I was typing it in wrong… but makes sense it’s not working because I’m using conditional formatting. Any fix or workaround to this?