[求助]根据条件格式的颜色统计单元格个数

anonym

to oobird:

如果你把条件格式的设置比较常数值5，改成单元格引用，而这个单元格的值是5。条件格式运行依然正确，而你的程序的输出结果为21，不是9！

特别当条件格式引用单元格的值不是数值，比如文本的话，boo = Application.Evaluate(frmlaA1)这句话会出错！

[此贴子已经被作者于2008-8-22 11:15:28编辑过]

anonym

重写代码如下

 Function ConditionalColor(rg As Range) As Long
Dim cel, cel1 As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla, f1, f2 As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long
Set cel = rg.Cells(1, 1)
Set cel1 = cel
    ConditionalColor = cel.Interior.ColorIndex
If cel.FormatConditions.count > 0 Then
      With cel.FormatConditions
        For i = 1 To .count
            frmla = .Item(i).Formula1
            f1 = Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1)
            If Not WorksheetFunction.IsNumber(rg) Then
                cel = Chr(34) & cel & Chr(34)
            End If
            Select Case .Item(i).Operator
            Case xlEqual
                frmla = cel & "=" & f1
            Case xlNotEqual
                frmla = cel & "<>" & f1
            Case xlBetween
                f2 = Right(.Item(i).Formula2, Len(.Item(i).Formula2) - 1)
                frmla = "AND(" & f1 & "<=" & cel & "," & cel & "<=" & f2 & ")"
            Case xlNotBetween
                f2 = Right(.Item(i).Formula2, Len(.Item(i).Formula2) - 1)
                frmla = "OR(" & f1 & ">" & cel & "," & cel & ">" & f2 & ")"
            Case xlLess
                frmla = cel & "<" & f1
            Case xlLessEqual
                frmla = cel & "<=" & f1
            Case xlGreater1
                frmla = cel & ">" & f1
            Case xlGreaterEqual
                frmla = cel & ">=" & f1
            End Select
            cel = cel1
            boo = Application.Evaluate(frmla)
            If boo Then
                On Error Resume Next
                    tmp = .Item(i).Interior.ColorIndex
                If Err = 0 Then ConditionalColor = tmp
                Err.Clear
                On Error GoTo 0
                Exit For
            End If
        Next i
    End With
End If
End Function

lizhigang160

好多天没有来，感谢各位的热心解答！！[em23][em23][em23]

mch0377

也希望得到答案。

killsoft

原帖由 anonym 于 2008-8-22 13:16 发表
重写代码如下

这个代码好像以前是哪个斑竹写的，而且注释是英文的，楼主改的不错，值得学习

liuyanjiu

如果不用VBA可以实现吗?

single_star

只管条件格式结果进行统计:
(不用管公式怎么设,只要选一个作用条件格式的单元格当代表即可进行统计)
http://club.excelhome.net/thread-477227-1-1.html

[ 本帖最后由 single_star 于 2009-9-19 09:38 编辑 ]

kim0583

现正需要这道题的答案，不知道有解了？

dhf104109

附件下来的，确实是算出来21

yiyiyicz

找了很久
看完还是一头雾水
似乎有出路