Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    Gainesville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Cells that contain red text (Win2k, Excel 2k SR-1)

    Is it possible with a function to count the number of cells in a given range in which there is red text? I set up a simple macro while entering data that would take a cell and apply two design formats: Selection.Font.ColorIndex=3 and Selection.Font.Bold=True just so I could pick out those cells easily. I'd love to have a count of all cells that match these conditions.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Count Cells that contain red text (Win2k, Excel 2k SR-1)

    Public Function CountRed(rngSelected As Range) As Double
    Dim rngCell As Range
    Application.Volatile 'oops, forgot this first post
    CountRed = 0
    For Each rngCell In rngSelected
    If rngCell.Font.ColorIndex = 3 Then CountRed = CountRed + 1
    Next rngCell
    End Function
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    May 2001
    Location
    Gainesville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Cells that contain red text (Win2k, Excel 2k SR-1)

    Perfect! It works! Thanks!

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Cells that contain red text (Win2k, Excel 2k SR-1)

    John,
    Out of curiosity, any significance to making the function Double rather than Long?

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Count Cells that contain red text (Win2k, Excel 2k SR-1)

    Haste, and because Long doesn't cover all the cells in one WS in the unlikely event a huge range is selected? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Cells that contain red text (Win2k, Excel 2k SR-1)

    Thanks. I just wondered if there might be some (microscopic) performance improvement of using Double versus Long, say.
    FWIW, in terms of not covering all the cells, are you thinking of Integer? Maximum value of a Long variable is > 2 billion, I think...

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Count Cells that contain red text (Win2k, Excel 2k SR-1)

    Colin, I think that I wasn't thinking!
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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