If you are one of those who use Excel, you are probably interested in knowing how to automatically count or add Excel cells or cells with colors. If that is your case, then you are in the right place. We have carefully prepared this guide so that you can learn how to do this.
What should you know about VBA?
If you are used to using Excel, you should know how all the formulas are used in a spreadsheet. Go to Formulas, in the category Statistics more functions> there is a function called count, which is precisely the one that is responsible for counting the number of cells within an interval that meet certain conditions.
The only drawback is that this function has criteria limited only to texts or numbers. But, with VBA, the language of Microsoft Visual Basic macros, you will have the opportunity to create a function that allows you to count the number of cells with not so limited criteria.
VBA It gives you the opportunity to create a user-defined function (UDF) and that you can also save in a plug-in file so that you can use it again perhaps in other books and even transfer them to another computer.
How to create a FDU to count the color of cells?
The steps to follow to be able to add color to the cells are very simple and we will proceed to show you what they are. For many, this program is sometimes a bit slow for them, and if that is your case you can make Word and Excel faster when loading in Windows and then be able to follow these steps without problems.
The first thing you should do is open Microsoft Excel and then press the Alt + F11 buttons so you can see the Visual Basic editor window.
You must click on the Insert menu and select module to precisely create a module and write the following script: Function CountCcolor (range_data As range, criteria As range) As Long Dim datax As range Dim xcolor As Long xcolor = criteria.Interior.ColorIndex For Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountCcolor = CountCcolor + 1 End If Next datax End Function
Now you must close the window Virtual Basic and return to Excel. Next you must test the UDF. In order to do this, you must create sample data that, of course, contain cells with different colors.
You may also be interested in:
You must go to cell D3 and write the following function: = CountCcolor (range_data, criteria) Now in the argument ‘range_data’ you must select cell C2 to C51 and in the argument ‘criteria’ select cell F11
Press Enter and you will see that the result of the cell F2 is 6. This means that the number of cells that will be in blue are 6.
Of course you can use other colors. For example, you can change the color of cell F1 and add the one you prefer using the home page fill color.
If what you want is to compress the UDF in such a way that you can use it in any computer or book, you just have to follow this next step.
You must select the File and then click Save As. This way you will have saved the book. Then you must select the Excel add-in (.XLAM) as format and you must also name the file.
Install the plugin. To achieve this you must open Microsoft Excel on your computer and open the dialog box ‘Excel Add-ins’ for Excel 2013 and later in the ‘Developer’ tab Add-ins in Excel 2010. Go to the Add-Ins dialog box and select Browse.
Go to the file location where the save plugin fileEither a folder, a USB drive, etc. And choose the file. When you have it, you must click open.
You must make sure that in this plugins dialog box, the checkbox is disabled. Once done, click OK.
You may also be interested in:
And that’s it, you will already have your cell colors UDF installed so you can use it whenever you want. Also, every time you want to use this function in any of your work in Excel, you simply have to place the arrow of your cursor in one of the cells of your spreadsheet and write the following script: =CountCcolor (range_data, criteria)
I'm sorry this content was not useful for you!
Let me improve this content!
Tell me, how can I improve this content?