Asked by:
How to count cells with certain font color in Excel without using VBA?

I have used cells with certain font color/format in many sheets. In the front sheet i want formula which should count if the cells font is in certain color. say for ex, if the cell A1 in sheet3 is in red color it should be counted in sheet1 where i have applied the formula.
I need formula without VBA code. Because when I use VBA code i'm not able to save the excel in normal Excel2007 workbook format. It has asked me to save it in macroenabled workbook format i.e xlsm or in 2003 format.
Solution to any of the above queries is appreciable.
Thanks,
Suresh J
Question
All replies

Bonsour®
"Suresh J_2010" a écriti want formula which should count if the cells font is in certain color.
say for ex, if the cell A1 in sheet3 is in red color it should be counted in
sheet1 where i have applied the formula.
I need formula without VBA code.No VBA ???
;o)))
Use XL4 function !!!GET.CELL(24,Target) get font ColorIndex of target
How to :
Select Cell B1
define Name : FONTCOLORIDX
refers to : =GET.CELL(24,A1) ' Beware relative address no $$
OKUsage
in any cell
=FONTCOLORIDX
!!! no parenthesis nor parameter
you get the font colorIndex of the immediate current left celli.e
G5 with font color Red
H5=FontcolorIdx >display : 3then with colorindex value you are able to calculate related information(COUNTIF, SUMIF, SUMPRODUCT...)
Warning :
Change color or font is not an event for calculation
You must push F9 to enable color change and have valuable results
Maude Este 
Thank you Maude.
I have tried the same but for all the color the value is displayed as 1 only. In the Sheet1, there are range that contain values and among them some are colored in Red color. Now I want formula in "B1" cell, that the count of Red color cells in that range.

Hi All,
I finally decided to use VBA for counting the colored cells in a sheet. For this I have created a custom function in the module as follows:
Function countcolor(rng As Range, colorindex As Integer)
Dim count As Integer
count = 0
For Each rw In rng
If rw.Font.Color = colorindex Then
count = count + 1
End If
Next rw
countcolor = count
End FunctionIn my excel sheet, I simply using this as formula "=countcolor($E$5:$E$9,255)" for counting red color font cells, but here one more problem araised. When I changed the font color in the desired range, the formula is not updating automatically. I have to select the cell through "F2" and press "Enter". The "Ctrl + Alt + F9" also not working.
I cannot edit the formula cells always since I'm gonna use this formula at many places.
Any help regarding this is appreciable.
Thanks,
Suresh J

I have used this and similar formulas successfully, however, after they are saved, closed then opened again.. this message appears when trying to enter the formula "This formula takes no arguments"
Any suggestions on what I am doing wrong would be greatly appreciated....


Hi All,
I finally decided to use VBA for counting the colored cells in a sheet. For this I have created a custom function in the module as follows:
Function countcolor(rng As Range, colorindex As Integer)
Dim count As Integer
count = 0
For Each rw In rng
If rw.Font.Color = colorindex Then
count = count + 1
End If
Next rw
countcolor = count
End FunctionIn my excel sheet, I simply using this as formula "=countcolor($E$5:$E$9,255)" for counting red color font cells, but here one more problem araised. When I changed the font color in the desired range, the formula is not updating automatically. I have to select the cell through "F2" and press "Enter". The "Ctrl + Alt + F9" also not working.
I cannot edit the formula cells always since I'm gonna use this formula at many places.
Any help regarding this is appreciable.
Thanks,
Suresh J
