Count the cells if colored red

[Vasu]

HI

I'm not sure how to proceed with this. I've sheets with 3000 rows and each
row having data uptill the DV column. Each row has a few cells colored in
red. I want to know the number of cells colored in red for each row. Tried
to use Countif function however count if does not support color formatting.
Is there a macro which can used to get the data for each row or a formula to
do the same.

Answers will be helpful.

Regards
Sid/Vasu.

 

[Per Jessen]

Hi

You can use this UDF to count colored cells.

Syntax:

=CountIfColor(A1:B10,C1)

Where A1:B10 is the range to count and C1 has the color to count for.

Public Function CountIfColor(CellsToCount As Range, Color As Range) As
Variant
Dim CountColor As Long
Dim count As Long
If Color.Cells.count > 1 Then
CountIfColor = CVErr(2023)
Exit Function
End If
CountColor = Color.Interior.ColorIndex

For Each cell In CellsToCount
If cell.Interior.ColorIndex = CountColor Then
count = count + 1
End If
Next
CountIfColor = count
End Function

Regards,
Per

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

 

[Per Jessen]

Hi

The UDF has to inserted into a general module (Insert > Module), not
ThisWorkbook module.

Regards,
Per

 

[Vasu]

I've one thing to say

Really Superb

Thank you so much, you saved so much of time for me.

Regards
Sid/Vasu

 

[Per Jessen]

Thanks for your reply. I am glad to help.

Regards,
Per

"Vasu" <[email protected]> skrev i meddelelsen
I've one thing to say

Really Superb

Thank you so much, you saved so much of time for me.

Regards
Sid/Vasu