Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Formula, function problem (Excel 2000)

    Hi all,

    I'm trying to count a certain color in multiple ranges, it works this function works fine from the worksheet, with one range, but when I try to add ranges to it, it doesnt' work......anyone?

    CBC((InRange As Range, WhatColorIndex As Integer, Optional strsht As String, Optional OfText As Boolean = False) As Long
    Dim Rng As Range
    Application.Volatile True

    For Each Rng In InRange.Cells
    If OfText = True Then
    CBC = CBC - (Rng.Font.ColorIndex = WhatColorIndex)
    Else
    CBC = CBC - (Rng.Interior.ColorIndex = WhatColorIndex)
    End If
    Next Rng
    End Function

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

    Re: Formula, function problem (Excel 2000)

    Do you mean that you want to supply multiple discontiguous ranges? You can do that like this:

    =CBC((D3:F7,I10:K18),1)

    This will count the number of cells with black background in the range consisting of D3:F7 and I10:K18.

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

    Re: Formula, function problem (Excel 2000)

    Also, if the range name has multiple noncontiguous areas, you will need to use the Areas Method such as in <post#=405395>post 405395</post#>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Formula, function problem (Excel 2000)

    Hans

    What does the 1 stand for?
    =CBC((D3:F7,I10:K18),1)

    and, could I include more discontiguous ranges?

    Thanks, Darryl.

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

    Re: Formula, function problem (Excel 2000)

    1. The 1 is the WhatColorIndex argument to the CBC function. Excel has a 56 color palette, numbered 1 to 56. In the default palette, 1=Black, 2=White, 3=Red, 4=Green etc. So the 1 specifies black as color.

    2. You can use up to 31 (I think) discontiguous areas, for example replace the InRange argument (D3:F7,I10:K18) by (D3:F7,I10:K18,A22:C25,F38:W97)

Posting Permissions

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