CountColor -- Match to Cell Font Color

[richard blaine]

Trying to count items in a column based on the font color of a cell. For instance, if A1= "Xcolor" font and there are five "Xcolor" font cells B1:B100, then C1=5 (C1 contains the formula). Thanx

 

[Jonmo1]

Check out the ASAP Utilities Add-In. It has that functionality..

Here's a sticky post regarding Addins and other things recommended by MrExcel.
http://www.mrexcel.com/forum/showthread.php?t=126629

Hope that helps...

 

[Domski]

Here's a UDF that will do what you want:

Function CountFontColour(Range As Range, CCode As Double) As Double
    Dim YourDataRange As Range
    Dim kount As Double
    Dim cell As Range
    Application.Volatile
    Set YourDataRange = Intersect(Range.Parent.UsedRange, Range)
    kount = 0
    For Each cell In YourDataRange
        If cell.Font.ColorIndex = CCode Then kount = kount + 1
    Next cell
    CountFontColour = kount
End Function

Use it like: =CountFontColour(B1:B100,3) to count red coloured writing.

For a list of colour codes run this on a blank woorksheet:

Sub GetColours()
    Dim x As Integer
 
    Application.ScreenUpdating = False
        For x = 1 To 56
            Cells(x, 1) = x
            Cells(x, 2).Interior.ColorIndex = x
        Next x
    Application.ScreenUpdating = True
End Sub

Hope it helps,

Dom

 

[Lewiy]

Try using this custom formula:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o:p></o:p>

Function CountColour(iRange As Range, FindColour As Range)<o:p></o:p>
Dim LookupColour As Long, iCount As Long<o:p></o:p>
Dim r As Range<o:p></o:p>
LookupColour = FindColour.Interior.ColorIndex<o:p></o:p>
iCount = 0<o:p></o:p>
For Each r In iRange<o:p></o:p>
    If r.Interior.ColorIndex = LookupColour Then<o:p></o:p>
        iCount = iCount + 1<o:p></o:p>
    End If<o:p></o:p>
Next r<o:p></o:p>
CountColour = iCount<o:p></o:p>
End Function<o:p></o:p>

<o></o>
<o> </o>
Then in the cell where you want the result, type:<o></o>
=CountColour(A2:A50,A1)<o></o>
<o> </o>
Where A2:A50 is the range you are checking and A1 is the cell whose colour you are checking for.<o></o>
<o> </o>
Hope that helps.<o></o>

 

[richard blaine]

Lewiy's formula is perfect for counting cell (fill) color but I would like the same for counting font color. Is that possible? Thanks

 

[Domski]

Did you try the function I posted?

 

[richard blaine]

Actually I found this one which works using this order: "CountColor(A1,B1:B100)"

Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Font.ColorIndex

For Each rCell In rSumRange
If rCell.Font.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell

CountColor = vResult
End Function