# Counting Cell Colors

#### coliervile

##### Well-known Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Norie

##### Well-known Member
Charlie

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

What are you actually trying to do?

#### coliervile

##### Well-known Member
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

##### New Member

Then write: =ASAPCellColorIndex(B3)

#### coliervile

##### Well-known Member
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

Replies
1
Views
205
Replies
25
Views
650
Replies
42
Views
971
Replies
3
Views
241
Replies
11
Views
425

1,191,165
Messages
5,985,036
Members
439,935
Latest member
Monty238

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back