Summing Values by Cell and Font Colors: -Learn how to use VBA code in Excel to create custom functions that allow you to sum values based on specific cell colours or font colours. This article provides step-by-step instructions and code examples to help you perform calculations based on colour-coded data in your spreadsheets.

Certainly! Here is the VBA code you can use to create two functions: SumCellsByColor
and SumCellsByFontColor
. These functions will allow you to sum values based on cell colour and font colour, respectively.
Function SumCellsByColor(data_range As Range, cell_color As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = cell_color.Cells(1, 1).Interior.Color
For Each cellCurrent In data_range
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByColor = sumRes
End Function
Function SumCellsByFontColor(data_range As Range, font_color As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = font_color.Cells(1, 1).Font.Color
For Each cellCurrent In data_range
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByFontColor = sumRes
End Function
To use these functions, follow these steps:
- Open your Excel workbook and press
Alt + F11
to open the VBA editor. - Insert a new module by clicking on
Insert
>Module
. - Copy and paste the above code into the module.
- Save the workbook as a macro-enabled file format (.xlsm) if prompted.
Now you can use the SumCellsByColor
and SumCellsByFontColor
functions in your Excel spreadsheets to sum values based on cell colour and font colour, respectively.
For example, suppose you have a range of values in cells A1 to A10, and you want to sum all the cells that have the same colour as cell B1. In cell B2, you can enter the formula =SumCellsByColor(A1:A10, B1)
. This will return the sum of all the values in the range A1:A10 that have the same colour as cell B1.

Similarly, you can use the SumCellsByFontColor
function by specifying the range of values and the cell with the desired font colour.
These custom functions provide a convenient way to perform calculations based on cell and font colors in Excel.