+ Reply to Thread
Results 1 to 9 of 9

Count number of cells with blue type

  1. #1
    Registered User
    Join Date
    06-04-2008
    MS-Off Ver
    2007
    Posts
    37

    Count number of cells with blue type

    hey guys, im trying count the number of cells that have blue text (color index 5) inside of them, and then show the amount in (B2), but for the life of me i cant figure this out...help!!!

    thanks

    shy

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Count number of cells with blue type

    Place this code in a Standard module
    Please Login or Register  to view this content.
    In B2 type

    =bluecount(A1:G10)

    Replace A1:G10 with the range you have used
    Last edited by royUK; 12-10-2009 at 03:28 PM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Count number of cells with blue type

    Hello shy07014,

    Since you didn't say if you were counting cells that all the text was blue or not, this macro will count either a cell whose text all matches the color index you are looking for, or if the cell contains one or more characters with that same color index. Like Roy's macro, this too can be used as worksheet formula.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    06-04-2008
    MS-Off Ver
    2007
    Posts
    37

    Re: Count number of cells with blue type

    they are both great, except in Leith Ross's how can i change the color value if ui wanted to? also, if i wanted to put this code in a commandbutton, how would i accomplish this?

    thanks again

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Count number of cells with blue type

    Hello shy07014,

    To add either code to a CommandButton requires changing the code to either look at the current cells selected or examine the used cells on the worksheet. Which do you want to do?

  6. #6
    Registered User
    Join Date
    06-04-2008
    MS-Off Ver
    2007
    Posts
    37

    Re: Count number of cells with blue type

    all the used cell in the spreadsheet, please.

    thanks

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Count number of cells with blue type

    Hello shy07014,

    You will need to either attach the macro to the button if it is a Forms type button or place a call the macro in the Click event of the button if it is a Control Toolbox button.

    Example using Roy's Code
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-04-2008
    MS-Off Ver
    2007
    Posts
    37

    Re: Count number of cells with blue type

    ok, i tried this:
    Please Login or Register  to view this content.
    however it returns a compile error: "function call on left hand side of assignment must return variant or object

  9. #9
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Count number of cells with blue type

    Try this bit more extream but works perfectly
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1