SUMFORMAT

There are 4 functions


SUMFORMAT

Returns the sum of all the values that have been formatted with multiple attributes.


Adds the cells that have a certain font color.
If you afterwards change the font color in any of the referenced cells, you have to press Ctrl+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.


Parameters:
reference = The range of cells to search in.
color_index_nr = The cell that has the font color to sum, or the color index number (1-56) from Excel.



SUMFORMAT_CELLCOLOR

Returns the total value of the numbers that have been formatted with the same colour.

'rgeSearchCells - The range of cells you want to check
'rgeCriteriaCell - The cell with the colour to check

Public Function SUMBYCELLCOLOR(ByVal rgeSearchCells As Range, _
                              ByVal rgeCriteriaCell as Range) as Double
Dim rgeCell As Range
For Each rgeCell In rgeSearchCells
   If rgeCell.Interior.Color = rgeCriteriaCell.Interior.Color Then
      SUMBYCELLCOLOR = SUMBYCELLCOLOR + rgeCell.Value
   End If
Next rgeCell
End Function

Adds the cells that have a certain fill color.
If you afterwards change the color in any of the referenced cells, you have to press Ctrl+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.


Parameters:
reference = The range of cells to search in.
color_index_nr = The cell that has the fill color to sum, or the color index number (1-56) from Excel.




SUMFORMAT_FONTBOLD

Returns the sum of all the values that have been formatted in bold.


SUMFORMAT_FONTCOLOR

Returns the total value of the numbers that have been formatted with the same font colour

'rgeSearchCells - The range of cells you want to check
'rgeCriteriaCell - The cell with the colour to check

Public Function SUMBYFONTCOLOR(ByVal rgeSearchCells As Range, _
                              ByVal rgeCriteriaCell as Range) as Double
Dim rgeCell As Range
For Each rgeCell In rgeSearchCells
   If rgeCell.Font.Color = rgeCriteriaCell.Font.Color Then
      SUMBYFONTCOLOR = SUMBYFONTCOLOR + rgeCell.Value
   End If
Next rgeCell
End Function



© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext