Count by font color

[anamika sharma]

Dear Sir/ma'am

anyone can tell me how can count only color font cell in filtered range... or how to create function number in excel for own formula..

 

[SirJB7]

Hi, anamika sharma!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, if you haven't performed yet the search herein, try going to the topmost right zone of this page (Search...), type the keywords used in the title when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

Regards!

PS: Next time please start topic at proper forum. Thank you.

 

[vijay.vizzu]

Dear Anamika Sharma

Please find below code for your reference. It is a UDF function. In Rcolor, just refer your font which is colored. In RRange just select your range in which colored font cells are there and lastone Sum, if you want to sum the values then just put TRUE otherwise FALSE, False will use as count colored font cells. If any doubt please let me know

Function ColorFunction1(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

lCol = rColor.Font.Color
    If SUM = True Then
      For Each rCell In rRange
        If rCell.Font.Color = lCol Then
                vResult = WorksheetFunction.SUM(rCell) + vResult
        End If
      Next rCell
    Else
        For Each rCell In rRange
        If rCell.Font.Color = lCol Then
                vResult = 1 + vResult
        End If
      Next rCell
End If
ColorFunction1 = vResult
End Function

 

[Smallman]

Pretty confident the above link originated at Ozgrid. Old post by Dave.

http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

Even the variables and construct is the same.

Take care

Smallman

 

[anamika sharma]

Dear Vijay sir,, i have used this method...and this work for total count...in a range...but i need count according to filter...i attached a sample file..plz chek it.

 

[vijay.vizzu]

Dear Smallman

Sorry for this... but in this forum, we need help and need to help.

 

[vijay.vizzu]

Dear Anamika

Which excel version you are using, if you are using excel 2007 or above, then excel has built in function (filter by color) then you can select and count.

 

[anamika sharma]

Dear Vijay sir,
i m using excel 2007, .but i m using a userform to show subtotal value in mis label...
when i change filter from userfrom..then all filter change on worksheet....so i need red count on filtered range...besided filtered by color....

 

[rumshar]

Hi,
My Version is here..
With Regards
Rudra

 

[Debraj]

Try this..

* Name Manager
* Create a Name : "ColoredCell"
* Use Refer To as : =GET.CELL(24,OFFSET(INDIRECT("A1"),ROW()-1,0))
* In C1 Use fomula as : =coloredCell

You can view the changes..
But it required F9 to update,
BTW, it’s a VBA, not Formula bases Approach..

 

[anamika sharma]

Thanks Debraj Sir,

 

[anamika sharma]

and Thanks to Chandorg.for solve my excel prob.

 

[Debraj]

Hi anamika..

Welcome back.. and thanks for sharing feedback.. (Its better to be late, than never.. )

 

[anamika sharma]

Good Morning, Sir/frnds.
i need help about...multiple users use one userform at same time..and data stored one worksheet.and if one user open userform with serial no..10, and 2 user open same userform then serial auto change 11 it is possible..