Count by font color
This tutorial shows how to count cells that have a specific font color by creating a User Defined Function which is then applied as an Excel function
Example: Count by font color
Function CountByFontColor(Data As Range, CellRefColor As Range)
'declare a variable
Dim CellColor As Long
Dim CurrentCell As Range
Dim CountFont As Long
Dim CellColor As Long
Dim CurrentCell As Range
Dim CountFont As Long
Application.Volatile
CountFont = 0
CellColor = CellRefColor.Font.ColorIndex
CountFont = 0
CellColor = CellRefColor.Font.ColorIndex
For Each CurrentCell In Data
If CellColor = CurrentCell.Font.ColorIndex Then
CountCell = CountCell + 1
End If
CountCell = CountCell + 1
End If
Next CurrentCell
CountByFontColor = 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 font 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 font 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 font 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 font color that you want to count for.
|
=CountByFontColor(B5:C11,E5)
|
This function was created in VBA, as per the VBA code above, that counts and returns the number of cells that have a specific font color in the selected range (B5:C11). 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 font color you want to count for. In this example cell (E5) captures the font 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 font 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 font color.
This tutorial shows how to count the number of cells that contain a specific font 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 font color.
FORMULA
=CountByFontColor(data_rng,specific_color)
=CountByFontColor(data_rng,specific_color)
ARGUMENTS
data_rng: Range of cells to test for colored font.
specific_color: The font color to count for.
data_rng: Range of cells to test for colored font.
specific_color: The font color to count for.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count by cell color | How to count cells that have been highlighted 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 |