我找到了一个宏,它计算有条件格式的彩色单元格。
'Variable declaration
Dim lColorCounter2 As Long
Dim rngCell2 As Range
'loop throughout each cell in the range
For Each rngCell2 In Selection
'Checking Amber color
If Cells(rngCell2.Row, rngCell2.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
lColorCounter2 = lColorCounter2 + 1
End If
Next
MsgBox "Green =" & lColorCounter2
我正在尝试将其更改为函数。我在这里读到,这可能是因为.DisplayFormat.Interior.Color
不适用于函数。有解决方法吗?
理想情况下,我希望该函数有两个参数,要搜索颜色的单元格范围,第二个是要查找的颜色的单元格。
请记住
:
- RGB(255, 192, 0) 不是绿色,而是接近橙色。
- 更改要循环的范围 - rng (现在 rng 等于 Sheet1.Range("A1:A20"))
尝试:
Option Explicit
Public Function Color(ByVal rng As Range)
Dim Counter As Long
Dim Cell As Range
For Each Cell In rng
'Checking Amber color
If Cells(Cell.Row, Cell.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
Counter = Counter + 1
End If
Next
MsgBox "Orange=" & Counter
End Function
Sub test()
Dim rng As Range
Set rng = Sheet1.Range("A1:A20")
Call Color(rng)
End Sub