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, "
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


" 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, "
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.