Excel Count Colored Cells with VBA ColorIndex
To count colored cells in Excel using VBA color codes, use this code.
This code will check for VBA color index in each cell for the worksheet. To start with, color the cell A1 with red and run the macro. It will count all the cells with red background color and give the result.
If you want to count cell with different color, then change the color of cell A1. (example: Green, Yellow or any color from color palette. Here is the code.
How to Count Cells by Color? or Sum Cells by Color?
This one function in this section will CountbyColor and SumBycolor, both the functions based on the background color or Font color of a cell. The below function is a UDF that can be used in a Excel worksheet directly, once you copy paste this to a new module in VB editor.
Function VBA_Sum_Count_Cell_Color_Font_Color(rCells As Range, rReference As Range, sOption As String) As Long
Dim rCell As Range, rColIndex As Long, retVal As Long
'Get Option From the User
If sOption = "" Then
MsgBox "Option is missing"
Exit Function
End If
retVal = 0
sOption = VBA.UCase(sOption)
'Process Option and perform Sum of Count
Select Case sOption
Case "COUNTCCOLOR"
rColIndex = rReference.Interior.ColorIndex
For Each rCell In rCells
'COUNT Cells by Color Index of Reference Cell Background Colorindex
If rCell.Interior.ColorIndex = rColIndex Then
retVal = retVal + 1
End If
Next rCell
Case "SUMCCOLOR"
rColIndex = rReference.Interior.ColorIndex
For Each rCell In rCells
'SUM Cells by Color Index of Reference Cell Background Colorindex
If rCell.Interior.ColorIndex = rColIndex Then
retVal = retVal + rCell.Value
End If
Next rCell
Case "COUNTFCOLOR"
rColIndex = rReference.Font.Color
For Each rCell In rCells
'COUNT Cells by Color Index of Reference Cell Font Colorindex
If rCell.Font.Color = rColIndex Then
retVal = retVal + 1
End If
Next rCell
Case "SUMFCOLOR"
rColIndex = rReference.Font.Color
For Each rCell In rCells
'SUM Cells by Color Index of Reference Cell Font Colorindex
If rCell.Font.Color = rColIndex Then
retVal = retVal + rCell.Value
End If
Next rCell
Case Else
MsgBox "Incorrect option specified"
Exit Function
End Select
'Return Sum or Count of Cells Matching Reference Cell ColorIndex
VBA_Sum_Count_Cell_Color_Font_Color = retVal
End Function
Function Count_Cell_Color(rCells As Range, rReference As Range) As Long
Count_Cell_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "countCcolor")
End Function
Function Count_Font_Color(rCells As Range, rReference As Range) As Long
Count_Font_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "countFcolor")
End Function
Function Sum_Cell_Color(rCells As Range, rReference As Range) As Long
Sum_Cell_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "sumCcolor")
End Function
Function Sum_Font_Color(rCells As Range, rReference As Range) As Long
Sum_Font_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "sumFcolor")
End Function
The main count by color function is ‘VBA_Sum_Count_Cell_Color_Font_Color’ which is called by 4 different functions at the bottom of the code. This will give you the understanding of the parameters used in this function.
If you want to know more about VBA Colorindex or Color codes, refer this link.