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

来源：互联网发布：贾巴尔数据编辑：程序博客网时间：2024/06/05 12:26

1、EXCEL－开发工具中Visual Basic

2、Sheet1上右键插入模块

3、代码粘贴

Function CountByBGColor(Col As Range, CountRange As Range) '根据背景颜色计数Application.Volatile'用于将用户自定义函数标记为易失性函数，无论何时在工作表的任意单元格中进行计算时，易失性函数都必须重新进行计算。'非易失性函数只在输入变量改变时才重新计算，若不用于计算工作表单元格的用户自定义函数中，则此方法无效。Dim iCell As RangeCountByBGColor = 0For Each iCell In CountRange    If iCell.Interior.Color = Col.Interior.Color Then        CountByBGColor = CountByBGColor + 1    End IfNextEnd FunctionFunction SumByBGColor(Col As Range, SumRange As Range) '根据背景颜色求和Application.VolatileDim iCell As RangeSumByBGColor = 0For Each iCell In SumRange    If iCell.Interior.Color = Col.Interior.Color Then        SumByBGColor = SumByBGColor + Application.WorksheetFunction.Sum(iCell)    End IfNextEnd FunctionFunction CountByFontColor(Col As Range, CountRange As Range) '根据字体颜色色计数Application.VolatileDim iCell As RangeCountByFontColor = 0For Each iCell In CountRange    If iCell.Font.Color = Col.Font.Color Then        CountByFontColor = CountByFontColor + 1    End IfNextEnd FunctionFunction SumByFontColor(Col As Range, SumRange As Range) '根据字体颜色求和Application.VolatileDim iCell As RangeSumByFontColor = 0For Each iCell In SumRange    If iCell.Font.Color = Col.Font.Color Then        SumByFontColor = SumByFontColor + Application.WorksheetFunction.Sum(iCell)    End IfNextEnd Function

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

例：=SumByFontColor(J45,M35:P41)

0 0