vba - 仅使用 VBA 选择可见单元格的范围

Question

我想在状态跟踪器上使用以下函数“CountCcolor()”。我的目的是能够使用该函数在可见单元格的单个列范围中查找有多少以特定颜色突出显示，比如绿色。

Function CountCcolor(range_data As Range, criteria As Range) As Long

    Dim datax As Range
    Dim xcolor As Long

    ' The next one-liner does not work. Without it, it selects visible and hidden cells. I only want it to select visible cells:
    range_data = Selection.SpecialCells(xlCellTypeVisible).Select

    xcolor = criteria.Interior.ColorIndex
    For Each datax In range_data
        If datax.Interior.ColorIndex = xcolor Then
             CountCcolor = CountCcolor + 1
        End If
    Next datax
End Function

提前谢谢你的帮助！

标签： vbarangeexcel-2010cellvisible

Answer 1

不要使用Interior.ColorIndex

相反，只需使用Interior.Color. 我也被这一次难住了。简而言之，ColorIndex代表的是一种颜色，而不是一种独特的颜色。请参阅此处了解更多详细信息

Function CountCcolor(range_data As Range, criteria as Range) As Long

Dim myRange As Range, myCell As Range, TempCount As Long
Set myRange = range_data.SpecialCells(xlCellTypeVisible)

For Each myCell In myRange
    If myCell.Interior.Color = criteria.Interior.Color Then
        TempCount = TempCount + 1
    End If
Next myCell

CountCcolor = TempCount

End Function