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:
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 + F11to open the VBA editor.
- Insert a new module by clicking on
- 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
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.