Master Counting Colors in Excel – From Beginner to VBA Pro

Let’s say we want to count the red cells in the Tetris Grid.

In the cell for red color count (R9 in the image), type =countcolor(.

This is a User Defined Function (UDF) that we create and add to the Excel workbook. We can use it like an ordinary Excel function like SUM or XLOOKUP.

Pick a cell with the color to count (say, the red cell in Q9). We’re still editing the formula, so add a comma then select the data range (cells B8:M28 in the image).

Press Enter. Boom, our UDF counts 25 red cells.

If we want to fill a range, press F2 to edit. Select the data range (B8:M28), hit F4 to lock it ($B$8:$M$28), and fill down. All colors are counted fast.

Try counting all those colors with the methods in Levels 1 - 3 and you’ll be working for a long time! With a User Defined Function we can do it super fast.

Let’s peek under the hood. Press Alt + F11 to open the Visual Basic Editor. The Project Explorer lists your project and modules. The Properties window shows the module name.

New to Excel VBA? Right-click the project or objects folder. Choose Insert > Module.

Name the module something like “Count_Color” and note that we can’t include spaces in module names, but we can use underscores, so that’s why I called the module “Count_Color” and not “Count Color”.

Our star is a User-Defined Function (UDF) called CountColor.

Here’s the code that you can copy and paste into the code window:

The code starts with Function and ends with End Function. That marks the start and end of our User Defined Function (”UDF”).

Here’s the first line:

Function CountColor(rng_Ref As Range, rng_Data As Range) As Long

The inputs are the color cell (rng_Ref) and data range (rng_Data).

The output is a long integer (As Long).

The code inside the function checks each cell’s interior color against the reference color. If there is a match, the function adds 1 to CountColor.

Imagine counting purples in the Tetris grid. We scan each cell from the top left corner, and then move to the right so we check every cell in the first row, then move to the next row down, and so on until we’ve checked all the cells.

Our UDF does the same thing.

When it meets the first purple cell, it calculates CountColor = 0 + 1.

And the next purple cell? CountColor = 1 + 1.

By the end, you’ve got the total number of purple cells.

The UDF returns the value of CountColor to your Excel sheet.

We added a single line of code: Application.Volatile.

This updates the function on recalculation. If we don’t include it, then the color count doesn’t change even if we recalculate the sheet. When it’s there, it tells Excel that we need to recalculate the result of this function every time the sheet is recalculated.

The green line above it is a comment, so it doesn’t really count as a line of code. It’s ignored when Excel runs the VBA and it’s there so we can write reminders to ourselves or other human readers to make it easier to understand what’s going on.

Rename CountColor to CountColor2 everywhere inside the UDF because we changed the name of the function, so we need to update the code to run properly.

Let’s also add this subroutine:

VBA can do a lot more! Here’s pixel art of the Golden Gate Bridge from Pexels.com, made with the “Pixel Art” add-in.

Get it via Home > Add-ins > More Add-ins.

Search “PixelArt,” install it, and upload an image. It pixelates into Excel.

If the image is too large to fit on your screen you can zoom out with CTRL + ALT + Minus (repeat as many times as needed to zoom out). You can also zoom in with CTRL + ALT + Plus.

We can pick individual colors, like light blue, and use CountColor2 to count them.

But I also created more VBA in two separate modules to analyze the colors in the pixelated image in more comprehensive ways. All of the code is in the downloadable file (scroll to the top to get it).

The first Module is called “Z_Advanced_1”. It contains three procedures. Note that “GetAllCellColors” is a UDF so it can’t be run directly. Instead it needs to be used the same way as CountColor() in Levels 4 and 5.

The second Module is called “Z_Advanced_2”. It has one Sub procedure called “CreatePixelatedColorListGrouped” that groups the colors into buckets by similar hue so it’s easier to get an overview of how the colors are distributed. And there are three helper functions to do color calculations.

This is what you get when you run the Sub procedure “CreatePixelatedColorListGrouped” and choose 30 color buckets:

Excel VBA is powerful. Want to learn it? Check out my VBA course for beginners. Automate your work and save time!

In 2011, I was working under intense pressure with a high-stakes deadline. The company’s board of directors needed updated reports for an important meeting, and I had just minutes to deliver them.

My hands were cold and clammy as I held a stack of papers like a shield outside the VP’s office. Inside, the VP—my boss’s boss—was reviewing the first version of the reports with a red pen, ready to mark up any mistakes.

Suddenly, his computer dinged with an email: there was a last-minute change that had to be reflected in the reports.

I scrambled back to my desk. There were only 30 minutes left before the meeting. I opened Excel, clicked a few buttons to run my macros, and watched as they flawlessly updated the reports in minutes.

Without VBA, this task would have taken hours, and I would have missed the deadline.

Instead, the reports were ready with 20 minutes to spare. The VP commended my quick turnaround, and I realized in that moment how much VBA had saved my job and transformed my career.

Another pivotal moment came years earlier, back when I was working at a billion-dollar tech company.

I was in the open floor office, staring at a colleague’s laptop screen during a meeting. She opened an Excel file and then clicked a button that brought up the VBA editor.

At first glance, the editor looked overwhelming, filled with a ton of code that seemed completely foreign to me.

But then she began explaining what it all meant. The macros she had written were automating hours of repetitive work, saving her time every single week.

She showed me how it helped her prepare reports by handling data preparation, formatting, and analysis with just a few clicks. These reports were critical and went straight to the company’s senior executives, including the CEO and CFO.

Her words struck me deeply: “This is the only way I can meet my Monday afternoon deadline. Without these macros, I’d never finish in time.”

And then she added, “By the way, these reports are about to become your responsibility. You’ll need to learn VBA fast if you want to succeed in this role.”

That moment was a turning point.

Suddenly, VBA didn’t look so intimidating. It looked like a powerful cheat code that could make my life easier and let me meet those critical deadlines.

From then on, I made it my mission to learn VBA, and it transformed not only how I worked but how I viewed Excel as a tool for solving problems.

The truth is that most Excel users have no idea that VBA even exists, or they see it as something that's just too hard to learn.

But I'm here to help you take that next step and learn VBA for yourself. I spent years figuring out how to teach VBA so there's nothing to stop you.

More than 500 Excel users have taken my Excel VBA course, and now they use VBA to automate their work. And that could be you in a few weeks' time.

My course has over 10 hours of video tutorials taking you from the basics of macros to doing some advanced tasks with VBA. It also includes practice files and quizzes to keep the concepts fresh and give you the much needed practice to get good at Macros and VBA.

The course is split into three levels:

In Level 1 just like teaching someone to drive a car, I start with the basics of car control and road awareness. This means recording macros and learning how to use the VBA editor to write and edit code.

In Level 2 we take the car out onto public roads. Just like a learner driver practices to handle road signs and traffic, in Excel, I show you important ways to handle macros and VBA.

And in Level 3 we jump into the fast lane and turbocharge what you learned in earlier levels. You start to make reports fly out and then automate your work based on the foundations you learned in Levels 1 and 2.

When you can write VBA code efficiently and confidently it will change the trajectory of your career or business.

So, if you're ready to transform the way you work in Excel and automate repetitive tasks, check out the link to my online course. And I hope to see you inside soon!