I have a worksheet with entries of different font colours. Is there a
way to count the number occurences of a particular font colour within
the string in each cell?
For example:

cell a1 has the entry 'Bluered'. The first four charcters are in blue
font (41) and the last 3 in red (3).
I want a way to count how many of the text characters are blue = 4.

I hope this makes sense.

Thanks.

Hi
This will count blues

Function CountBlue(StringRange As Range) As Long
Dim i As Long, Temp As Long

Temp = 0
If Trim(StringRange.Value) <> "" Then
For i = 1 To Len(StringRange.Value)
If StringRange.Characters(Start:=i, Length:=1).Font.ColorIndex = 5
Then
Temp = Temp + 1
End If
Next i
End If
CountBlue = Temp
End Function

You can use it in a worksheet as
=CountBlue(A1)

to count blue characters in A1
regards
Paul

On Jul 3, 2:49*pm, "kabimeis...@googlemail.com"
<kabimeis...@googlemail.com> wrote:
> I have a worksheet with entries of different font colours. *Is there a
> way to count the number occurences of a particular font colour within
> the string in each cell?
> For example:
>
> cell a1 has the entry 'Bluered'. The first four charcters are in blue
> font (41) and the last 3 in red (3).
> I want a way to count how many of the text characters are blue = 4.
>
> I hope this makes sense.
>
> Thanks.

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

Call the function as such:
=CountByColor(A1:E11,3)

Before the comma is the range that you are counting and after the comma is
the color (3=red).
See this for more info.
http://www.mvps.org/dmcritchie/excel/colors.htm



Regards,
Ryan---

--
RyGuy


"(E-Mail Removed)" wrote:

> Hi
> This will count blues
>
> Function CountBlue(StringRange As Range) As Long
> Dim i As Long, Temp As Long
>
> Temp = 0
> If Trim(StringRange.Value) <> "" Then
> For i = 1 To Len(StringRange.Value)
> If StringRange.Characters(Start:=i, Length:=1).Font.ColorIndex = 5
> Then
> Temp = Temp + 1
> End If
> Next i
> End If
> CountBlue = Temp
> End Function
>
> You can use it in a worksheet as
> =CountBlue(A1)
>
> to count blue characters in A1
> regards
> Paul
>
> On Jul 3, 2:49 pm, "kabimeis...@googlemail.com"
> <kabimeis...@googlemail.com> wrote:
> > I have a worksheet with entries of different font colours. Is there a
> > way to count the number occurences of a particular font colour within
> > the string in each cell?
> > For example:
> >
> > cell a1 has the entry 'Bluered'. The first four charcters are in blue
> > font (41) and the last 3 in red (3).
> > I want a way to count how many of the text characters are blue = 4.
> >
> > I hope this makes sense.
> >
> > Thanks.
>
>