COUNTIF: criteria is a format

[alligatormaki]

I have a column with cells that have text of different colors. I'm trying to
count up the number of cells that the text is formatted a particular color.
For example, A1 and A4 are formatted red text, while A2, A3, and A5 are
formatted blue text. I need it to tell me that there are 2 cells with red
and 3 cells with blue. Thanks.

 

[krcowen]

It wouldn't be too difficult to write a macro to loop through a range
of cells and count the number of red and blue cells. I am not sure if
you would want to just count red and blue and put the numbers in
specific cells, or keep track of all the colors that are present.

If there is some explainable logic as to why the cells that are red
are red and why the cells that are blue are blue, you may find it more
beneficial to make the cell font turn red or blue through the use of
conditional formatting and do your counting with the same logic in a
cell on the spreadsheet as you have in the "formula" in each of your
conditional format conditions. That would effectively count the red
and blue cells, with out the need for a macro; and it could save some
time in adjusting the font.

Good luck
Ken
Norfolk, Va

 

[krcowen]

It wouldn't be too difficult to write a macro to loop through a range
of cells and count the number of red and blue cells. I am not sure if
you would want to just count red and blue and put the numbers in
specific cells, or keep track of all the colors that are present.

If there is some explainable logic as to why the cells that are red
are red and why the cells that are blue are blue, you may find it more
beneficial to make the cell font turn red or blue through the use of
conditional formatting and do your counting with the same logic in a
cell on the spreadsheet as you have in the "formula" in each of your
conditional format conditions. That would effectively count the red
and blue cells, with out the need for a macro; and it could save some
time in adjusting the font.

Good luck
Ken
Norfolk, Va

 

[vezerid]

Maki,

You can use this UDF next to your data:

Function GetCellColor(r as range) as Integer
GetCellColor = r.font.colorindex
End Function

Now, next to A1 you can enter the formula:

=getcellcolor(A1)

Red is 3, Blue is 5.

You can then count red cells with a formula like:
=COUNTIF(B:B,3)

HTH
Kostis Vezerides

 

[vezerid]

Maki,

You can use this UDF next to your data:

Function GetCellColor(r as range) as Integer
GetCellColor = r.font.colorindex
End Function

Now, next to A1 you can enter the formula:

=getcellcolor(A1)

Red is 3, Blue is 5.

You can then count red cells with a formula like:
=COUNTIF(B:B,3)

HTH
Kostis Vezerides