Excel Count by Color
By Richard Rost

4 years ago

How to Count Cells by Color in Microsoft Excel

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this Excel Fast Tip video, I'll show you how you can use a little VBA to count the cells in a range based on their color.

Resources

Source Code - Gold Members Only

Learn More!

Questions?

Please feel free to post your questions or comments below. Thanks.

Keywords

#msexcel, #microsoftexcel, #microsoft excel, #excel, #exceltips, Count Colored Cells, Count Cells by Color, Count number of cells with specific cell color, excel vba, countbycolor function

Comments for Excel Count by Color

Age	Subject	From
4 years	Count Cells by Color in Access	Richard Rost
4 years	Excel Count by Color	Kevin Robertson

Subscribe to Excel Count by Color
Get notifications when this page is updated

Intro

In this video, I will show you how to count cells by their background color in Microsoft Excel using a custom VBA function. We will create a user-defined function that can identify and total the number of cells of each color, whether the colors are applied manually or with conditional formatting. I will walk you through enabling the Developer tab, opening the VBA editor, writing and saving the CountByColor function, and using it directly in your worksheet to get fast color-based counts.

Transcript

In today's video, I'm going to show you how to count cells in your Excel spreadsheets by their color using a little bit of VBA. We're going to write our own function. We're going to have some fun. Here we go.

Let's say I've got a column of cells here, and I've got them assigned various colors for whatever reason. These could be colors that you manually assigned, or they could be colors that are assigned by conditional formatting. There are any number of reasons you might want to count the number of each color in this column.

The easiest way to do this is to make your own count color function, which we can do with a little bit of VBA. So I'm going to make a little key over here. Let's say I want to count the red, the green, and the yellow cells. I'll use the format painter just to copy over the formats. There's red, there's green, and there's yellow.

To make our own function, we're going to have to have the Developer tab on. If you don't have the Developer tab on, right-click somewhere on your menu bar, go to Customize the Ribbon, and then in here just check on the Developer tab. Click OK, and now you'll see this Developer tab. Now we can add our own VBA functions and all kinds of cool stuff.

Come over here and click on Visual Basic. This will open up the VBA editor. We're going to come to this little button here, drop this guy down, and we're going to insert a module. The module is where you can put your VBA code. We're going to create our own function, so I'm going to type in Public Function. That just means everybody can use it. CountByColor is the name I'm giving it.

We're going to send it two bits of information: one is the range we're going to be counting in, and the second parameter is going to be the target cell's color or the target cell itself.

So, cellRange as Range and then targetCell as Range as well.

Now I need to track the target color, which is the color of the target cell. I'm going to need a variable to track the count as I count up, and then I need a temporary variable just to track what specific cell we are on. So it's going to be Dim targetColor As Long, count As Long, and c As Range. "c" will be my individual cell that I'm on as I'm looping through this range.

Now, targetColor is going to be equal to targetCell.Interior.ColorIndex. "Interior" is a fancy word for background, the background color. ColorIndex gives me the background color number. Every color's got its own number assigned. We don't have to worry about what those numbers are. But that says of the target cell, this guy right here or whichever one I point at, give me the background color index number.

Now I'm going to loop through this range and count up the number of cells that have that same color. So, For Each c In cellRange, do some stuff, then Next c.

What's the stuff I want to do? Well, if c.Interior.ColorIndex equals the targetColor, then count equals count plus one.

So, loop down each one of these cells. For each "c" in cellRange, each cell in that range of cells, check its background color and see if it's equal to that. We probably could have done this without setting up a separate variable for it, but it's not a big deal.

When we're all done, we're going to set the value of the function CountByColor equal to whatever that count is, right there. And that's it. That's not very tough. We could have done this with probably one, two, three, or four lines of code. It makes it a little easier to do it.

Save this (Ctrl+S). Since this has a macro in it, some VB code macro, we have to change the workbook type, down to a macro-enabled workbook. An XLSM file. I'll call this MyCountByColor. Hit Save.

Now we can get rid of this or just minimize it for now. We're going to go back there and put in our actual function: type equals CountByColor. There's my function. First is the range: click and drag over that range there or you can use named ranges, whatever. I'm going to hit F4 to make that an absolute reference, and if you don't know what these terms are, come watch my full Excel courses. Absolute references, conditional formatting, all that stuff - I cover a lot of topics in my Excel courses.

Comma. The second variable that it needs is the target cell. Close that up, press enter, and look at that: there are three red cells in there. Auto fill that down. There you go, there's one green and two yellow. That is how you make a custom function to count cells by color.

That's it for today, folks. I hope you learned something. If you want to learn more Excel, stop by my website: ExcelLearningZone.com. I'll put a link in the description down below the video. I've got dozens of lessons on all kinds of different topics, from beginner to advanced: XLOOKUP, VLOOKUP, PivotTables, you name it. ExcelLearningZone.com.

Welcome to another Fast Tips video brought to you by ExcelLearningZone.com. I am your instructor Richard Rost.

Quiz

Q1. What is the main goal of the video tutorial?
A. To create a chart from colored cells in Excel
B. To count cells in Excel based on their color using VBA
C. To learn about XLOOKUP formulas
D. To automate email sending from Excel

Q2. Why do we need to turn on the Developer tab in Excel for this tutorial?
A. To enable Excel themes
B. To access conditional formatting
C. To insert and run VBA code
D. To protect individual worksheets

Q3. What function does the tutorial create in VBA?
A. CountCells
B. CountColors
C. CountByColor
D. ColorCounter

Q4. What are the two parameters required for the CountByColor function?
A. The cell font and row number
B. The worksheet name and a color code
C. The cell range to count in and a target cell for the color
D. The cell text and a comparison value

Q5. What property is used in VBA to get the background color of a cell?
A. cell.Background
B. cell.Interior.ColorIndex
C. cell.Fill.Color
D. cell.Shape.Color

Q6. What type of Excel file must you save your workbook as in order to use VBA macros?
A. .xlsx (Excel Workbook)
B. .csv (Comma Separated Values)
C. .xls (Legacy Excel)
D. .xlsm (Macro-Enabled Workbook)

Q7. What is the purpose of looping through each cell in the selected range in the function?
A. To sum the values of each cell
B. To change the color of each cell
C. To check if the cell's color matches the target color and count it if it does
D. To rename each cell

Q8. How do you use the custom CountByColor function in Excel after creating it?
A. By typing =CountByColor() into a cell with the required arguments
B. By recording a new macro
C. By creating a new sheet
D. By exporting to PDF

Q9. Why might you use F4 while entering the formula in Excel?
A. To switch worksheet tabs
B. To make a cell reference absolute
C. To fill down a formula
D. To open the VBA editor

Q10. Where can you learn more about Excel topics like absolute references and conditional formatting according to the instructor?
A. In the comments section below the video
B. By exploring Excel's built-in help
C. At ExcelLearningZone.com
D. On the worksheet itself

Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-D; 7-C; 8-A; 9-B; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone covers how to count cells by color in your Excel spreadsheets through a simple VBA solution. I will guide you through writing your own custom function to accomplish this task.

Suppose you have a column filled with different colored cells. These colors might be assigned manually or through conditional formatting, and you want to count how many cells of each color exist in that column. For example, you might need a count of red, green, and yellow cells.

The most straightforward way to do this is to create a custom CountByColor function using VBA. I like to make a visual key using the format painter to copy the red, green, and yellow formats onto separate cells to use as references for the colors we want to count.

Before writing any VBA code, make sure you have the Developer tab activated in Excel. If you do not see the Developer tab, you can enable it by right-clicking on the ribbon, selecting Customize the Ribbon, and checking the Developer box.

Once the Developer tab is visible, open the Visual Basic editor. From there, insert a new module. This is where you will write your VBA code. We are creating a function named CountByColor, which takes two pieces of information: the range of cells to scan and a target cell that holds the color you want to count.

Within the function, we set up variables. One variable holds the color of the target cell, another tracks the count, and a third is used to loop through each cell in the given range. The target color is determined by checking the Interior.ColorIndex property of the target cell, which tells you the index number corresponding to its background color.

Next, the code loops through each cell in your chosen range, comparing the color of each cell to the target color. Each time a match is found, the count is incremented. Once the loop completes, the function returns the count of matching colored cells.

After writing the code, remember to save your workbook as a macro-enabled file, typically with the .xlsm extension, so that your custom function will work.

To use your new function, simply enter it into a cell using the format =CountByColor(range, target cell). For range, select the group of cells you are interested in, and for the target cell, select one of your color key cells, such as the red, green, or yellow reference cells you created earlier. Press enter, and you will see the count of cells matching the target color. You can autofill the formula for other color references, and you will get a tally for each color.

This demonstrates how to create a useful custom function in Excel for counting cells based on their background colors, whether the color was applied manually or by conditional formatting.

If you want more in-depth Excel lessons, covering everything from basic to advanced topics such as XLOOKUP, VLOOKUP, and PivotTables, visit my website at the link below for comprehensive video tutorials with step-by-step instructions on everything discussed here.

Live long and prosper, my friends.

Topic List

Counting cells by color using VBA
Creating a custom CountByColor function
Enabling the Developer tab in Excel
Opening and using the Visual Basic Editor
Inserting a new module for VBA code
Declaring variables in VBA for cell color counting
Getting the ColorIndex of a cell
Looping through a range in VBA
Comparing cell background colors using ColorIndex
Returning the count from a custom function
Saving a workbook as macro-enabled (XLSM)
Using the custom function in Excel formulas
Setting absolute references in Excel formulas
Using autofill to copy custom function results

Links

Resources

Learn More!

Questions?

Keywords