excel - 使用 VBA 计算范围内彩色单元格的数量

Question

我有一个计算彩色单元格数量的代码。你能建议如何扩大范围吗？基本上我需要计算彩色单元格列范围，如（“A：Z”）和 nRowIndex = 2 到 100？

Sub CountCellsWithBackgroundColor()
  Dim nRowIndex As Integer, nCellNumber As Integer

  'Go through the range
  Worksheets("Report_Rule_S").Activate
  For nRowIndex = 2 To 100
    If Range("E" & nRowIndex).Interior.ColorIndex <> -4142 Then 'need to do somthing like Range("A:Z")
      nCellNumber = nCellNumber + 1
    End If
  Next nRowIndex

  ' Output the result
  Worksheets("cover").Range("B12") = nCellNumber
End Sub

标签： excelvba

Answer 1

您可以将所有这些放在一个 For-Each 循环中：

Dim aCell As Range

Dim cellNumber As Integer
cellNumber = 0

For Each aCell In Range("A2:Z100").Cells:
  If aCell.Interior.ColorIndex <> -4142 Then
    cellNumber = cellNumber + 1
  End If
Next