Count number of cells with certain background color

[John]

How do I count the number of cells in a range with a certain interior color
index, say 65535 (yellow). I would like to return that result in a worksheet
cell. Thanks

 

[Mike H]

John,

If these are conditional format colours then a different technique is
required but for cell shading put the code below in a general module and call
with

=countcolour(A1:A10)

Function CountColour(rng As Range) As Long
For Each c In rng
If c.Interior.ColorIndex = 6 Then
CountColour = CountColour + 1
End If
Next
End Function


Mike

 

[JLGWhiz]

If the color was set as Range("a1").Interior.ColorIndex then you can use:

Sub clrndx()
Dim c As Range, x As Long
For Each c In Range("A1:C65536")
If c.Interior.ColorIndex = 3 Then 'Red
x = x + 1
MsgBox x
End If
Next
End Sub

If the color was set by conditional format it is a little mor sticky and is
probably easier to check for the criteria that sets the color than the color
itself.

 

[Joel]

Or this UDF

Function GetColors(ColorNum As Long, Target As Range)

GetColors = 0
For Each cell In Target
If cell.Interior.colorindex = ColorNum Then
GetColors = GetColors + 1
End If

Next cell
End Function