Counting Cell Colors

[coliervile]

I'm using the following macro to color a range of cells:

Private Sub CellColor()
Range("A1:J19").Select
With Selection.Interior
.ColorIndex = 1
End With
End Sub

and the following formula to count the cells with the color index of "1" in a range:

=SUMPRODUCT(--(ColorIndex(J1:J19)=1))

but I'm getting an error #NAME? (Formula contains unrecognized text) and can't figure out why??? I wouild appreciate your help.

Best regards,

Charlie

 

[Norie]

Charlie

Could it be because there isn't actually a worksheet function called ColorIndex?

What are you actually trying to do?

 

[coliervile]

Thanks Norie for you reply. I'm wanting to count the number of cells in a range (e.g. J1:J19) that are colored black (IndexColor 1).

To answer your other question is no, there's no worksheet function named ColorIndex.

Best regards,

Charlie

 

[RG FL]

try downloading the ASAP utility.

Then write: =ASAPCellColorIndex(B3)

 

[Norie]

Charlie

I know there is no in-built Excel worksheet function called ColorIndex.

Perhaps this link might help Functions For Working With Cell Colors.

 

[coliervile]

I've put this funtion in the worksheet:

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

and this formula in cell:

=COUNTBYCOLOR(J1:J19,1,FALSE)

and I stiil get the same error- #NAME?

Can you see any reason why???

Best regards,

Charlie