count font color

  • Thread starter Thread starter snowing
  • Start date Start date
S

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
 
MsgBox COUNTBYCOLOUR(Range("A1:A10"),3,TRUE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
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
 
msgbox cstr(countbycolor(intersect(activesheet.Range("A1:A200"),3,False))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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)
 
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)
 
Getting a type mismatched error on the 3 .
msgbox cstr(countbycolor(intersect(activesheet.Range("A1: A" &
your_var),3,False)
 
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)
 
Back
Top