Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Count Interior Color

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to count all cells in a range that contain an interior color regardless of color.

    Thanks
    Paul Walton
    USAF

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The solution to this is different based on conditional formatting or straight-up formatting.

    This link is for straight-up formatting:

    http://www.mrexcel.com/board/viewtop...c=1585&forum=2

    (Bottom post)

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-01 09:50 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Straight-Up Formatting

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The link above should be of serious use then. Post back w/ additional questions or concerns.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Works good for only 1 color. I need it to work for a cell that contains a any color in a range.

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    Code:
    Function CountByColor(InputRange As Range)
    Dim cl As Range, TempCount As Double
    Application.Volatile
    TempCount = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
    If cl.Interior.ColorIndex <> xlNone Then TempCount = TempCount + 1
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    CountByColor = TempCount
    End Function
    Now just use =countbycolor(a1:z25), and redo the range to your liking.

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-01 10:11 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks... Works as advertised

    Paul Walton
    USAF

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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