Count by Color Using VBA in Microsoft Excel

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.

Code explanation

Application.Volatile is used as it will recalculate every time a cell value is changed in the workbook.

DefinedColorRange.Interior.ColorIndex

Above code is used to get the interior color of the defined range.

```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 info@exceltip.com

1. I followed the steps above and get #NAME? as the output. Any suggestions?

2. HOW CAN I GET IT TO COUNT BY COLOR IF I HAVE ANOTHER FORMULA FOR COUNIF THAT CHANGE'S THE BACKGROUND COLOR OF THE CELLS I WANT TO COUNT?

3. HOW CAN I GET IT TO COUNT BY COLOR IF I HAVE ANOTHER FORMULA FOR COUNIF THAT CHANGE'S THE BACKGROUND COLOR OF THE CELLS I WANT TO COUNT?

4. HOW CAN I GET IT TO COUNT BY COLOR IF I HAVE ANOTHER FORMULA FOR COUNIF THAT CHANGE'S THE BACKGROUND COLOR OF THE CELLS I WANT TO COUNT?

Terms and Conditions of use

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.