Thread: Formula, function problem (Excel 2000)

1. 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. 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. 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#>.

4. 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. 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
•