Sum by font color
To sum cells that have a specific font color we need to create a new function in Excel, with the use of VBA, that can then be applied as any other Excel function
Example: Sum by font color
Function SumByFontColor(Data As Range, CellRefColor As Range)
'declare a variable
Dim CellColor As Long
Dim CurrentCell As Range
Dim SumFont As Long
Dim CellColor As Long
Dim CurrentCell As Range
Dim SumFont As Long
Application.Volatile
SumFont = 0
CellColor = CellRefColor.Font.ColorIndex
SumFont = 0
CellColor = CellRefColor.Font.ColorIndex
For Each CurrentCell In Data
If CellColor = CurrentCell.Font.ColorIndex Then
SumCell = WorksheetFunction.Sum(CurrentCell, SumCell)
End If
SumCell = WorksheetFunction.Sum(CurrentCell, SumCell)
End If
Next CurrentCell
SumByFontColor = SumCell
End Function
ADDITIONAL NOTES
Note 1: This VBA code creates a new function that can be used to sum the number of cells that contain a specified font color. The first part of the funding is associated with the Data range. The second part of the function is associated with the cell reference that contains the specific font color that you want to sum for.
Note 1: This VBA code creates a new function that can be used to sum the number of cells that contain a specified font color. The first part of the funding is associated with the Data range. The second part of the function is associated with the cell reference that contains the specific font color that you want to sum for.
|
=SumByFontColor(B5:C11,E5)
|
This function was created in VBA, as per the VBA code above, that sums the values in each of the cells that are colored with the specified 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 sum for. In this example cell (E5) captures the font color that you want to sum for.
Note: for this function to work you must first create the VBA code, as per the VBA example. |
EXPLANATION
To sum the number of cells that contain a specific font color we first need to create a new function in VBA and write function in Excel.
To sum the number of cells that contain a specific font color we first need to create a new function in VBA and write function in Excel.
FORMULA
=SumByFontColor(data_rng,specific_color)
=SumByFontColor(data_rng,specific_color)
ARGUMENTS
data_rng: Range of cells to test for colored font.
specific_color: The font color to sum for.