[Mearon]

I'm have a spread sheet in Excel that has some cells fonts in red to indicate poor numbers. I want to know how I can count how many cells on the spread sheet have red font. I tried to do a COUNTIF command but I'm not sure if this is will count the colored font data.

[l99057j]

Google the following:

Count excel cells color

The first link that comes up is dead, but if you click on the Cached version you'll see it. It should be simple to modify this to use the foreground color rather than the background.

[nikeshtnt]

You can use the following macro and formula for this.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult



''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''



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

--------------------------------------------------------------------------------

You can now use the custom function (ColorFunction) like;

=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.

To COUNT these cells that have the same fill color as cell $C$1 you could use:

=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.

Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9