Excel VBA Functions: Summing Values by Cell and Font Colors

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:

1. Open your Excel workbook and press Alt + F11 to open the VBA editor.
2. Insert a new module by clicking on Insert > Module.
3. Copy and paste the above code into the module.
4. 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.

You May Also Like

Please Follow Us

Facebook –Click

YouTube – Click

Quora – Click