Count by Color?

Hi golf4:

From what you have stated, you then need to count the cells that house 1 (for Red Color), or 2 (for Blue Color), and you can use COUNTIF to do that -- no problem there.

You will have to make sure then that you are operating on the correct range, and the use of the formula will be ...

=COUNTIF(range,1) ... for Red Colored cells
=COUNTIF(range,2) ... for Blue Colored cells

What do you think? Please check it out and post back if it works for you and your colleague.

Thanks, Yogi, but.......

Hi, Yogi -

Thanks for the quick response - really appreciate it. But the way you mention is the way she is doing it now. I could have sworn I saw a posting, something like COUNTIF("the format code for the color/hue RED")......

I MUST BE GETTING OLD!!!!!!!!

golf - -

You may not know it, but there are two ways to count colors because there are two ways to "color" a cell.

One way is through conditional formatting, such as how it is reasonable to assume you meant when you described the fact that red cells have a 1 in them, and blue cells have a 2 in them. Yogi's formula would work because it tests for the condition of a corresponding numeric value in those cells.

Keep in mind, a conditionally formatted cell only has the appearance of a color change, but really, the color index is unchanged even when the condition changes and the appearing color changes with the condition.

If Yogi's formula does not work for you, then those cells are almost certainly shaded manually by format, not by condition.

So, a udf (user defined function) is what would be needed to count a range of cell's color index population. Here is one that's been posted on this board before. Paste it in a standard VBA module:


Public Function CountIfColor(rng As Range, clrindx As Integer)
Dim Cell
CountIfColor = 0
For Each Cell In rng.Cells
If Cell.Interior.ColorIndex = clrindx Then
CountIfColor = CountIfColor + 1
End If
Next Cell
End Function

To return the quantity of red-shaded (non-conditionally formatted) cells in the range A1:A100 for example, this is the formula you'd type into a cell:

=CountIfColor(A1:A100,3)

Note, the color index for red is 3.

Hi,

As a sample, I found Ivan F Moala's nice function. :D

counting "colored" cells
http://www.mrexcel.com/board2/viewtopic.php?t=6398&highlight=colorcell

this one fires off a worksheet change
(I couldn't get the those functions to work, my own also)
i couldn't get toms to refresh

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellCount As Integer
Dim cell As Range
Dim cRange As Range
Set cRange = Range("colorrange") ' colorrange named range of where your colors are
CellCount = 0
For Each cell In cRange
If cell.Interior.ColorIndex = 3 Then '3 is red
CellCount = CellCount + 1
End If
Next

Range("color_count").Value = CellCount ' color_count, named range of the cell with the count

End Sub

White, regarding the UDFs you couldn't get to refresh, and your code...

The UDFs work, but because formatting is not a triggerable event, the udfs are refreshed by editing the cell they are housed in (F2 & Enter; or changing the range & index arguments), or by using the fill handle on (in this case) a red-shaded cell to fill other cells with the same color in the range being evaluated. This would also refresh your code, as would an edit to any cell on the worksheet.

A sheet change code would need to "hard code" the cell address, even if it's at the end of a dynamic range.

The advantage of the udf (which maybe is why a formula was being requested) is that the user can place the formula anywhere they want, including on a spreadsheet other than the one containing the cells being evaluated, ie placing in a cell on Sheet1 the formula =CountIfColor(Sheet2!A1:A100,3). This means the users leave the code alone, always a good thing.

You probably knew this latter stuff, but just thought I'd respond FYI to your reference about how my and Ivan's UDFs can be refreshed.

AGAIN......

Thanks to everyone for their ideas. I really appreciate them!!!

Just curious, where did people get the numbers for the colors? Colo's code is excellent, but there are several colors not mentioned in his code....

Thanks,

Mike

Hi Mike, Welcome to the Board!

Pls look into "ColorIndex Property" in your help file.
FYI, you can get the pallete colors as follows.

'Place this code in your std module.

<font face=Courier New><SPAN style="color:darkblue">Option</SPAN> <SPAN style="color:darkblue">Explicit</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> GetPalleteColors()
<SPAN style="color:green">'// Written by Colo</SPAN>
    <SPAN style="color:darkblue">Dim</SPAN> lngCnt <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>, lngColors(1 <SPAN style="color:darkblue">To</SPAN> 56) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
    <SPAN style="color:darkblue">For</SPAN> lngCnt = 1 <SPAN style="color:darkblue">To</SPAN> 56
        lngColors(lngCnt) = ThisWorkbook.Colors(lngCnt)
    <SPAN style="color:darkblue">Next</SPAN>
    <SPAN style="color:darkblue">For</SPAN> lngCnt = 1 <SPAN style="color:darkblue">To</SPAN> 56
        <SPAN style="color:darkblue">With</SPAN> Cells(lngCnt, 1)
            .Interior.ColorIndex = lngCnt
            .Value = lngCnt
            .Offset(, 1).Value = lngColors(lngCnt)
            .Offset(, 2).Value = "#" & Right(("000000" & Hex(lngColors(lngCnt))), 6)
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
    <SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>