count font color

[snowing]

Hello,

Does anyone know how I could take the count font color code below, an
add it to a macro so that it will execute at the end of my macro an
displays a message box using "msgbox" with the total amount of cel
with the font color red with out having to ad
"=COUNTBYCOLOR(A1:A10,3,FALSE)" to a cell on the spread sheet.
Also, the range of cells with be different each time.

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

You can call this function from a worksheet cell with a formula like
=COUNTBYCOLOR(A1:A10,3,FALSE

 

[Bob Phillips]

MsgBox COUNTBYCOLOUR(Range("A1:A10"),3,TRUE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[colofnature]

To count the number of red cells in column A add this line to your
code:

msgbox
cstr(countbycolor(intersect(activesheet.usedrange,[a:a]),3,False))


Col

 

[snowing]

thanks guys for the help.

Colofnature, how can I have your statement end at the last cell and no
count all cells in column A
If I have from cell A1 to A200 (number of cell will be different eac
time) how can i have if stop counting at A200

 

[Bob Phillips]

msgbox cstr(countbycolor(intersect(activesheet.Range("A1:A200"),3,False))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[snowing]

thanks for the reply..
It may be 200 cells sometimes but may only be 150 to 100 cells anothe
time, and i can't tell that ahead of time.
I have a bit of code that counts up the cells and saves the count in
variable. Can i use the variable with the saved count in place of th
number, (example, A + my variable) ?

msgbox cstr(countbycolor(intersect(activesheet.Range("A1
A200"),3,False)

 

[Bob Phillips]

msgbox cstr(countbycolor(intersect(activesheet.Range("A1:A" &
your_var),3,False))




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[snowing]

Getting a type mismatched error on the 3 .
msgbox cstr(countbycolor(intersect(activesheet.Range("A1: A" &
your_var),3,False)

 

[Bob Phillips]

maybe wrap-around

msgbox cstr(countbycolor(intersect(activesheet _
..Range("A1: A" & your_var),3,False))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[snowing]

Nope, same thing.