Excel Tip > Excel Functions List > Custom Functions > Count by Color Using VBA in Microsoft Excel

Count by Color Using VBA in Microsoft Excel

Fb-Button

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.

ArrowMain

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.

ArrowOutput

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.

 

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

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

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

Previous
Next

Comments

  2. Daniel

    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?

    Reply ↓
  3. Elis

    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?

    Reply ↓
  4. Ron

    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?

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

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.