Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting formatted cells in a formula (excel 2003)

    Is there a way to count for example all "yellow" formatted cells in a column?
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: counting formatted cells in a formula (excel 2003)

    See Functions For Cell Colors on Chip Pearson's website.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting formatted cells in a formula (excel 2003)

    He has a good function that will work, and it does, great because I tried it. The one I'm using is: But still have a question that follows his method below

    ************************************************** ************************************************** *****************************************

    Counting Cells With A Specific Color

    The following function will return the number of cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False).

    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)

    ************************************************** ************************************************** *************

    How do I make a function in a module available to ALL workbooks if I cannot see the Personal workbook on the left hand side of the VBA window? Is it because no one has recorded a macro in excel on this computer before?

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: counting formatted cells in a formula (excel 2003)

    > Is it because no one has recorded a macro in excel on this computer before?

    Probably. See <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> by Legare Coleman.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting formatted cells in a formula (excel 2003)

    thank you HansV...both articles gave me everthing I needed to get the function into the personal workbook and available to the user on this computer in any excel worksheet they happen to be in. I was half way there, but your links made it go alot faster...at least I did it without banging my head for once. [img]/forums/images/smilies/smile.gif[/img]
    cheers,
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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