有没有人遇到过实际上可以与条件格式一起使用的功能?
有一些kutools和albebits的插件,但它们不是基于公式的(您必须手动选择所有内容)
我已经找到了,但是仅适用于手动格式化
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell) + vResult
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
最佳答案
接下来是@Jeeped和@Comintern ...
这对我有用-一个简化的示例:
Function WrapCountReds(rRange)
WrapCountReds = rRange.Parent.Evaluate("CountReds(" & _
rRange.Address(False, False) & ")")
End Function
'can't call this directly from a worksheet but can be called via evaluate
Public Function CountReds(rRange As Range)
Dim rCell As Range
Dim vResult
For Each rCell In rRange
If rCell.DisplayFormat.Interior.ColorIndex = 3 Then
vResult = 1 + vResult
End If
Next rCell
CountReds = vResult
End Function
工作表用法示例:
=WrapCountReds("A1:A100")