|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 0031126 于 2014-11-3 19:12 编辑
- Function SumInteriorColor(Rng As Range, X As Variant) '根据单元格底色求和
- Dim CorIndex As Integer, TempSum As Variant
- Dim Temp As Range, Rng1 As Range, Rng2 As Range
- Application.Volatile True
- If TypeName(X) = "Range" Then
- CorIndex = X.Interior.ColorIndex
- Else
- SumInteriorColor = "未知参数类型"
- Exit Function
- End If
-
- On Error Resume Next
- Set Rng = Intersect(Rng.Parent.UsedRange, Rng)
- On Error GoTo 0
-
- If Not Rng Is Nothing Then
- For Each Temp In Rng
- If Temp.Interior.ColorIndex = CorIndex Then
- TempSum = TempSum + Val(Temp.Value)
- End If
- Next Temp
- Else
- TempSum = 0
- End If
- SumInteriorColor = TempSum
- End Function
- Function CountInteriorColor(Rng As Range, X As Variant) '根据单元格底色计数
- Dim CorIndex As Integer
- Dim Temp As Range, Rng1 As Range, Rng2 As Range
- Application.Volatile True
- If TypeName(X) = "Range" Then
- CorIndex = X.Interior.ColorIndex
- Else
- CountInteriorColor = "未知参数类型"
- Exit Function
- End If
-
- On Error Resume Next
- Set Rng = Intersect(Rng.Parent.UsedRange, Rng)
- On Error GoTo 0
-
- If Not Rng Is Nothing Then
- For Each Temp In Rng
- If Temp.Interior.ColorIndex = CorIndex Then
- CountInteriorColor = CountInteriorColor + 1
- End If
- Next Temp
- Else
- CountInteriorColor = 0
- End If
- End Function
复制代码 按单元格底色进行计数。
语法
CountInteriorColor(单元格区域,一个具有底色的单元格)
注意:单元格底色变换不会导致工作表重新计算!
|
|