EXCEL-VBA：根据单元格字体、背景颜色计数、求和

1、EXCEL－开发工具中Visual Basic

2、Sheet1上右键插入模块

3、代码粘贴

Function CountByBGColor(Col As Range, CountRange As Range) '根据背景颜色计数
Application.Volatile
'用于将用户自定义函数标记为易失性函数，无论何时在工作表的任意单元格中进行计算时，易失性函数都必须重新进行计算。
'非易失性函数只在输入变量改变时才重新计算，若不用于计算工作表单元格的用户自定义函数中，则此方法无效。

Dim iCell As Range
CountByBGColor = 0
For Each iCell In CountRange
    If iCell.Interior.Color = Col.Interior.Color Then
        CountByBGColor = CountByBGColor + 1
    End If
Next

End Function

Function SumByBGColor(Col As Range, SumRange As Range) '根据背景颜色求和
Application.Volatile

Dim iCell As Range
SumByBGColor = 0
For Each iCell In SumRange
    If iCell.Interior.Color = Col.Interior.Color Then
        SumByBGColor = SumByBGColor + Application.WorksheetFunction.Sum(iCell)
    End If
Next

End Function

Function CountByFontColor(Col As Range, CountRange As Range) '根据字体颜色色计数
Application.Volatile

Dim iCell As Range
CountByFontColor = 0
For Each iCell In CountRange
    If iCell.Font.Color = Col.Font.Color Then
        CountByFontColor = CountByFontColor + 1
    End If
Next

End Function

Function SumByFontColor(Col As Range, SumRange As Range) '根据字体颜色求和
Application.Volatile

Dim iCell As Range
SumByFontColor = 0
For Each iCell In SumRange
    If iCell.Font.Color = Col.Font.Color Then
        SumByFontColor = SumByFontColor + Application.WorksheetFunction.Sum(iCell)
    End If
Next

End Function

5、四个函数的，第一个参数是格式参照的单元格，第二个参数是求和或计数的区域。

   例：=SumByFontColor(J45,M35:P41)