VBA统计条件格式指定颜色单元格报错#VALUE!求助

Hey there! Let's break down why your function is throwing that frustrating #VALUE! error and how to fix it.

The Root Cause #

Your original function uses datax.DisplayFormat.Interior.ColorIndex to check the visible color of a cell. The problem? Excel restricts User-Defined Functions (UDFs) — the ones you call directly from worksheet cells — from accessing the DisplayFormat property. This property ties to the worksheet's interface rendering, and UDFs are blocked from interacting with such elements to keep Excel stable and prevent unexpected behavior.

Solution 1: Use Worksheet Functions (Faster & Simpler) #

If your green conditional format is based on a clear formula (e.g., =A1>50), skip VBA entirely and use native Excel functions. For example:

• If your rule uses a simple comparison: =COUNTIF(A1:C10, ">50")
• For more complex formulas: =SUMPRODUCT(--(A1:C10>50)) (the double hyphen converts TRUE/FALSE to 1/0 for counting)

This approach is way more efficient than looping through cells with VBA, and it avoids UDF restrictions altogether.

Solution 2: Modified VBA Function (For Complex Conditional Formats) #

If your conditional format rules are too complex to replicate with worksheet formulas, use this adjusted function. It checks conditional format rules directly (instead of relying on DisplayFormat) and works as a UDF:

Function CountColorWithConditionalFormat(rng As Range, targetColorIndex As Integer) As Long
    Dim cell As Range
    Dim cfRule As FormatCondition
    Dim matchCount As Long
    matchCount = 0
    
    For Each cell In rng
        Dim isConditionalMatch As Boolean
        isConditionalMatch = False
        
        ' Check each conditional format rule applied to the cell
        For Each cfRule In cell.FormatConditions
            If cfRule.AppliesTo.Intersect(cell) Is Not Nothing Then
                ' Evaluate the rule's formula for the current cell
                If Evaluate(Replace(cfRule.Formula1, cfRule.AppliesTo.Cells(1).Address, cell.Address)) Then
                    If cfRule.Interior.ColorIndex = targetColorIndex Then
                        isConditionalMatch = True
                        Exit For ' Stop checking rules once we find a match
                    End If
                End If
            End If
        Next cfRule
        
        ' Count if cell has direct green fill OR conditional green fill
        If cell.Interior.ColorIndex = targetColorIndex Or isConditionalMatch Then
            matchCount = matchCount + 1
        End If
    Next cell
    
    CountColorWithConditionalFormat = matchCount
End Function

How to Use It #

In any worksheet cell, enter:
=CountColorWithConditionalFormat(A1:C10, 43)
Replace A1:C10 with your target range and 43 with your desired ColorIndex.

Key Notes #

• I switched the return type from Integer to Long to avoid overflow errors if you're counting a large number of cells.
• This function checks both conditional format colors and direct cell fill colors. If you only want conditional format colors, remove the cell.Interior.ColorIndex = targetColorIndex part.
• For rules like data bars or color scales, you'll need to adjust the logic slightly (since their Formula1 works differently), but this works for most standard conditional format rules.

内容的提问来源于stack exchange，提问作者ThomasMommsen