Like this, we can count cells with colors in excel. Then it will sum all the matched color cells and returns the result as a count of that color cell. read more,” it will loop through a provided range of cells and count the number cells which are exactly holding the same color as the provided cell color. It is a flexibility given to a user to design functions similar to those already provided in Excel. This is done by using VBA “ User Defined Function User Defined Function User Defined Function in VBA is a group of customized commands created to give out a certain result. Now come back to the worksheet where we have data and create three cells with color.
#How to count a color in excel code#
If Data_ = Cell_Color ThenĪll that you have to do is to copy the above code and paste it in your VBA module, as shown below. read more.īelow is the code I have already written to create a function to count cells based on color.Ĭode: Function ColorCount(ColorCell As Range, DataRange As Range)Ĭell_Color = We have told there is no built-in function, but the beauty is we can create our own function by using VBA coding Using VBA Coding VBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. #2 Create Function using VBA CodingĪs I told you, unfortunately, we don’t have any built-in function, which can help us to count cells based on color in excel. This count is 2, so we have an updated result. Since there are only two rows marked with Orange color, the SUBTOTAL function showing the count as three only similarly, choose another color and see whether it is working perfectly or not. As you can see above, our SUBTOTAL function is showing the count of the filtered rows only.Choose any of the colors, and we will have those colored rows.It will show up all the available colors to filter. Click on the drop-down list of filters to go to “Filter by Color”.For the above data, the structure applies a filter option.OK, now the count of these cells will 7.After selecting the subtotal method next, we need to choose a range of cells to be counted.Once we open the SUBTOTAL function, we are supplied with a function list under this since we are counting cells, choose the 2 – COUNT function.First, we need to apply a subtotal function, so at the end of the data, open the SUBTOTAL function in excel.If you want to know more about VBA Colorindex or Color codes, refer this link. This will give you the understanding of the parameters used in this function. The main count by color function is ‘VBA_Sum_Count_Cell_Color_Font_Color’ which is called by 4 different functions at the bottom of the code. Sum_Font_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "sumFcolor") Sum_Cell_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "sumCcolor")įunction Sum_Font_Color(rCells As Range, rReference As Range) As Long VBA_Sum_Count_Cell_Color_Font_Color = retValįunction Count_Cell_Color(rCells As Range, rReference As Range) As LongĬount_Cell_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "countCcolor")įunction Count_Font_Color(rCells As Range, rReference As Range) As LongĬount_Font_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "countFcolor")įunction Sum_Cell_Color(rCells As Range, rReference As Range) As Long 'Return Sum or Count of Cells Matching Reference Cell ColorIndex 'SUM Cells by Color Index of Reference Cell Font Colorindex 'COUNT Cells by Color Index of Reference Cell Font Colorindex 'SUM Cells by Color Index of Reference Cell Background Colorindex
'COUNT Cells by Color Index of Reference Cell Background Colorindex Function VBA_Sum_Count_Cell_Color_Font_Color(rCells As Range, rReference As Range, sOption As String) As Longĭim rCell As Range, rColIndex As Long, retVal As Long The below function is a UDF that can be used in a Excel worksheet directly, once you copy paste this to a new module in VB editor. This one function in this section will Countb圜olor and SumBycolor, both the functions based on the background color or Font color of a cell.
#How to count a color in excel how to#
How to Count Cells by Color? or Sum Cells by Color? (example: Green, Yellow or any color from color palette. If you want to count cell with different color, then change the color of cell A1. It will count all the cells with red background color and give the result. To start with, color the cell A1 with red and run the macro. This code will check for VBA color index in each cell for the worksheet. To count colored cells in Excel using VBA color codes, use this code. Excel Count Colored Cells with VBA ColorIndex