# How to count colored cells per each cell range?

1. ## How to count colored cells per each cell range?

Hi,

What macros is needed to count the number of yellow cells per each cell range? The attached Excel contains the sample data plus how the results should look after query.

Is there an alternative way to macros to get these results?

Thank you,
Gabriele

2. ## Re: How to count colored cells per each cell range?

Hi,

See the file, it uses UDF.

3. ## Re: How to count colored cells per each cell range?

You can insert this code in VBA:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

Then use this formula to count cells =colorfunction(\$B\$1;B1:P1;FALSE) or this to sum the values =colorfunction(\$B\$1;B1:P1;TRUE)

\$B\$1 is the cell which has the color you need.
B1:P1 is the cell range

4. ## Re: How to count colored cells per each cell range?

``Please Login or Register  to view this content.``
see the attached file

5. ## Re: How to count colored cells per each cell range?

Thank you, Siva, it works nicely.
Best,
Gabriele

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1