SUMFORMAT

There are 4 functions



Remarks

* For instructions on how to add a function to a workbook refer to the page under Inserting Functions
* The equivalent JavaScript function is SUMFORMAT



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.


Public Function SUMFORMAT( _ 
            ByVal rgeValues As Range, _
            ByVal rgeExampleCell As Range, _
   Optional ByVal bCheckBackGround As Boolean = True, _
   Optional ByVal bCheckFontColor As Boolean = False, _
   Optional ByVal bCheckFontBold As Boolean = False) As Integer

Dim rgecell As Range
Dim dbtotalvalue As Double
Dim bbackground_check As Boolean
Dim bfontcolour_check As Boolean
Dim bfontbold_check As Boolean

   dbtotalvalue = 0
   For Each rgecell In rgeValues
      bbackground_check = True
      bfontcolour_check = True
      bfontbold_check = True
   
      If (bCheckBackGround = True) Then
         If (rgecell.Interior.ColorIndex <> rgeExampleCell.Interior.ColorIndex) Then
            bbackground_check = False
         End If
      End If

      If (bCheckFontColor = True) Then
         If (rgecell.Font.ColorIndex <> rgeExampleCell.Font.ColorIndex) Then
            bfontcolour_check = False
         End If
      End If

      If (bCheckFontBold = True) Then
         If (rgecell.Font.Bold = False) Then
            bfontbold_check = False
         End If
      End If
   
      If (bbackground_check = True) And (bfontcolour_check = True) And (bfontbold_check = True) Then
         dbtotalvalue = dbtotalvalue + rgecell.Value
      End If
   
   Next rgecell
   
   SUMFORMAT = dbtotalvalue
End Function



SUMFORMAT_CELLCOLOR

Adds the cells that have a certain fill color.
Returns the total value of the numbers that have been formatted with the same colour.

'rgeValuesCells - The range of cells you want to check
'rgeExampleCell - The cell with the colour to check

Public Function SUMFORMAT_CELLCOLOR(ByVal rgeValues As Range, _
                              ByVal rgeExampleCell As Range) As Double
                              
Dim dbtotalvalue As Double
Dim rgecell As Range

   For Each rgecell In rgeValues
      If (rgecell.Interior.Color = rgeExampleCell.Interior.Color) Then
         dbtotalvalue = dbtotalvalue + rgecell.Value
      End If
   Next rgecell

   SUMFORMAT_CELLCOLOR = dbtotalvalue
End Function


SUMFORMAT_FONTBOLD

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


Public Function SUMFORMAT_FONTBOLD(ByVal rgeValues As Range) As Double 

Dim dbtotalvalue As Double
Dim rgecell As Range

   For Each rgecell In rgeValues
      If (rgecell.Font.Bold = True) Then
         dbtotalvalue = dbtotalvalue + rgecell.Value
      End If
   Next rgecell

   SUMFORMAT_FONTBOLD = dbtotalvalue
End Function


SUMFORMAT_FONTCOLOR

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

'rgeValues - The range of cells you want to check
'rgeExampleCell - The cell with the colour to check

Public Function SUMFORMAT_FONTCOLOR(ByVal rgeValues As Range, _
                              ByVal rgeExampleCell As Range) As Double
                              
Dim dbtotalvalue As Double
Dim rgecell As Range

   For Each rgecell In rgeValues
      If (rgecell.Font.Color = rgeExampleCell.Font.Color) Then
         dbtotalvalue = dbtotalvalue + rgecell.Value
      End If
   Next rgecell

   SUMFORMAT_FONTCOLOR = dbtotalvalue
End Function



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