In this article, we will create a custom function to count the number of cells in the range having defined colors.
For this example, sample data consists of a range which has values ranging between 100 and 1000. Values in the cells are highlighted in different colors. We want to find the count of each color.
To calculate the count of a specific color, we have created a custom function “CountByColor”. This function takes two range parameters as input. First range parameter defines the interior color of the cell and second range parameter defines the sample range.
Application.Volatile is used as it will recalculate every time a cell value is changed in the workbook.
Above code is used to get the interior color of the defined range.
Please follow below for the code
Function CountByColor(DefinedColorRange As Range, CountRange As Range) Application.Volatile 'Declaring variables Dim ICol As Integer Dim GCell As Range 'Getting the interior color of the cell ICol = DefinedColorRange.Interior.ColorIndex 'Looping through the defined range For Each GCell In CountRange If ICol = GCell.Interior.ColorIndex Then 'Getting the count of matching colored cell CountByColor = CountByColor + 1 End If Next GCell End Function
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.