Count by cell color
This tutorial shows how to count cells that have been highlighted by creating a User Defined Function which is then applied as an Excel function
Example: Count by cell color
Function CountByCellColor(Data As Range, CellRefColor As Range)
'declare a variable
Dim CellColor As Long
Dim CurrentCell As Range
Dim CountCell As Long
Dim CellColor As Long
Dim CurrentCell As Range
Dim CountCell As Long
CellColor = CellRefColor.Interior.ColorIndex
For Each CurrentCell In Data
If CellColor = CurrentCell.Interior.ColorIndex Then
CountCell = CountCell + 1
End If
CountCell = CountCell + 1
End If
Next CurrentCell
CountByCellColor = CountCell
End Function
ADDITIONAL NOTES
Note 1: This VBA code (User Defined Function) creates a new function that can be used to count the number of cells that contain a specified color. The first part of the function is associated with a Data range. The second part of the function is associated with a cell reference that contains a specific color that you want to count for.
Note 1: This VBA code (User Defined Function) creates a new function that can be used to count the number of cells that contain a specified color. The first part of the function is associated with a Data range. The second part of the function is associated with a cell reference that contains a specific color that you want to count for.
|
=CountByCellColor(B5:C13,E5)
|
This function was created in VBA, as per the VBA code above, that counts the number of cells that have a specific color in the selected range (B5:C13). The first part of the formula relates to the data range which you want to test for. The second part of the formula specifies which color you want to count for. In this example cell (E5) is colored with the specific color that you want to count for.
Note: for this function to work you must first create the VBA code (User Defined Function), as per the VBA example above, and save the workbook as an Excel Macro-Enabled Workbook. |
EXPLANATION
This tutorial shows how to count the number of cells that contain a specific color by initially creating a User Defined Function and then applying the created function in Excel. The User Defined Function in this example will count the number of cells in a selected range that contain a specific color.
FORMULA
=CountByCellColor(data_rng,specific_color)
=CountByCellColor(data_rng,specific_color)
ARGUMENTS
data_rng: Range of cells from which to count colored cells.
specific_color: The color to count for.
data_rng: Range of cells from which to count colored cells.
specific_color: The color to count for.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count by font color | How to count cells that have a specific font color using a User Defined Function | |
| Sum by cell color | How to sum cells that have a specific color using a User Defined Function | |
| Sum by font color | How to sum cells that have a specific font color using a User Defined Function |