i wish to count the number of cells (in a given range) that contain tx
that is the colour red. so far i have been given this code to place i
a standard module:

Function Color(rngField As Object, intColor As Integer)
Dim intCounter As Integer
Dim rngAct As Range
For Each rngAct In rngField
If rngAct.Interior.ColorIndex = intColor Then
intCounter = intCounter + 1
End If
Next rngAct
Color = intCounter
End Function

but I am now unable to progress any further. Do I have to use th
=countif command? if so wot would formular be? Do I have to call upo
this function in an excel cell?

thanks for any help :

--
Message posted from http://www.ExcelForum.com

Hi
use this formula the following way:
=Color(A1:A100,color_index)
where A1:A100 is your given range and color_index the color to count
(red should be 3)

Frank

i wish to count the number of cells (in a given range) that contain
txt that is the colour red. so far i have been given this code to
place in a standard module:

Function Color(rngField As Object, intColor As Integer)
Dim intCounter As Integer
Dim rngAct As Range
For Each rngAct In rngField
If rngAct.Interior.ColorIndex = intColor Then
intCounter = intCounter + 1
End If
Next rngAct
Color = intCounter
End Function

but I am now unable to progress any further. Do I have to use the
=countif command? if so wot would formular be? Do I have to call upon
this function in an excel cell?

thanks for any help :)


---
Message posted from http://www.ExcelForum.com/

no. to count the red cells in A1:A100

in B1 (as an example)
=Color(A1:A100,3)

--
Regards,
Tom Ogilvy

"lyriquid " wrote in message
...
i wish to count the number of cells (in a given range) that contain txt
that is the colour red. so far i have been given this code to place in
a standard module:

Function Color(rngField As Object, intColor As Integer)
Dim intCounter As Integer
Dim rngAct As Range
For Each rngAct In rngField
If rngAct.Interior.ColorIndex = intColor Then
intCounter = intCounter + 1
End If
Next rngAct
Color = intCounter
End Function

but I am now unable to progress any further. Do I have to use the
=countif command? if so wot would formular be? Do I have to call upon
this function in an excel cell?

thanks for any help :)


---
Message posted from http://www.ExcelForum.com/

thanks very much - works brilliantly

--
Message posted from http://www.ExcelForum.com

this code looks for the colour of the fill and not the actual colour o
the txt. wot would i have to change within the vb code to make i
search for the txt colour

--
Message posted from http://www.ExcelForum.com

Hi
use the following
Function Color_font(rngField As Object, intColor As Integer)
Dim intCounter As Integer
Dim rngAct As Range
For Each rngAct In rngField
If rngAct.Font.ColorIndex = intColor Then
intCounter = intCounter + 1
End If
Next rngAct
Color_font = intCounter
End Function

Frank



this code looks for the colour of the fill and not the actual colour
of the txt. wot would i have to change within the vb code to make it
search for the txt colour?

or this version, which does fill or font colour.

Function Color_font(rngField As Object, intColor As Integer, Optional Text
As Boolean = False)
Dim intCounter As Integer
Dim rngAct As Range
For Each rngAct In rngField
If Text Then
If rngAct.Font.ColorIndex = intColor Then
intCounter = intCounter + 1
End If
If rngAct.Interior.ColorIndex = intColor Then
intCounter = intCounter + 1
End If
End If
Next rngAct
Color_font = intCounter
End Function

=Color(A1:A100,3) returns the count of red filled cells, or
=Color(A1:A100,3, True) returns the count of red font cells

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
use the following
Function Color_font(rngField As Object, intColor As Integer)
Dim intCounter As Integer
Dim rngAct As Range
For Each rngAct In rngField
If rngAct.Font.ColorIndex = intColor Then
intCounter = intCounter + 1
End If
Next rngAct
Color_font = intCounter
End Function

Frank



this code looks for the colour of the fill and not the actual colour
of the txt. wot would i have to change within the vb code to make it
search for the txt colour?