I thought I could alter any number of function examples that count the cells in a range for either the Font color or the Cell.Interior.ColorIndex or both.

I want to count ANY ColorIndex in a range like C4:C14. Where the call for the function would be something like =ColorCnt(C4:C14).

So any cell in that range with any of the 56 color index numbers colors would get counted.

(A separate function for the font of any color might be useful also)

Thanks,
Howard

Hi Howard,

Am Wed, 24 Aug 2016 01:06:55 -0700 (PDT) schrieb L. Howard:

I thought I could alter any number of function examples that count the cells in a range for either the Font color or the Cell.Interior.ColorIndex or both.

I want to count ANY ColorIndex in a range like C4:C14. Where the call for the function would be something like =ColorCnt(C4:C14).

So any cell in that range with any of the 56 color index numbers colors would get counted.

try:

Function ColCnt(myRng As Range, Optional FC As Boolean) As Long
Dim rngC As Range

For Each rngC In myRng
Select Case FC
Case False
If rngC.Interior.ColorIndex < -4142 Then
ColCnt = ColCnt + 1
End If
Case True
If rngC.Font.ColorIndex < 1 Then
ColCnt = ColCnt + 1
End If
End Select
Next
End Function

You can call it in the sheet with
=ColCnt(C4:C14) for interior colorindex
or with
=ColCnt(C4:C14;1) for font colorindex


Regards
Claus B.
--
Windows10
Office 2016

try:

Function ColCnt(myRng As Range, Optional FC As Boolean) As Long
Dim rngC As Range

For Each rngC In myRng
Select Case FC
Case False
If rngC.Interior.ColorIndex < -4142 Then
ColCnt = ColCnt + 1
End If
Case True
If rngC.Font.ColorIndex < 1 Then
ColCnt = ColCnt + 1
End If
End Select
Next
End Function

You can call it in the sheet with
=ColCnt(C4:C14) for interior colorindex
or with
=ColCnt(C4:C14;1) for font colorindex


Hi Claus,

Thanks. That works perfectly with the two calls you indicate.

I do have a problem with the function call formulas in a row (row 18 & pulled across many columns) =ColCnt(C4:C17) because adding a color to a cell does not fire the function formula.

I tried adding a macro

Sub aCalc()
ActiveSheet.Calculate
End Sub

and then call aCalc at the beginning and the end of the macro that inserts the color and some text into target cells.

It does not count the first color insertion, but counts any subsequent color insertions. So, if I have 4 colors in the column, the formula returns 3.

Any ideas?

Howard

Hi Howard,

Am Wed, 24 Aug 2016 03:21:40 -0700 (PDT) schrieb L. Howard:

I do have a problem with the function call formulas in a row (row 18 & pulled across many columns) =ColCnt(C4:C17) because adding a color to a cell does not fire the function formula.

try it this way:

Function ColCnt(myRng As Range, Optional FC As Boolean) As Long
Dim rngC As Range

Application.Volatile
For Each rngC In myRng
Select Case FC
Case False
If rngC.Interior.ColorIndex < xlNone Then
ColCnt = ColCnt + 1
End If
Case True
If rngC.Font.ColorIndex < xlAutomatic Then
ColCnt = ColCnt + 1
End If
End Select
Next
End Function

If you change the color you can press F9 to calculate new.


Regards
Claus B.
--
Windows10
Office 2016

On Wednesday, August 24, 2016 at 3:30:40 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Wed, 24 Aug 2016 03:21:40 -0700 (PDT) schrieb L. Howard:

I do have a problem with the function call formulas in a row (row 18 & pulled across many columns) =ColCnt(C4:C17) because adding a color to a cell does not fire the function formula.

try it this way:

Function ColCnt(myRng As Range, Optional FC As Boolean) As Long
Dim rngC As Range

Application.Volatile
For Each rngC In myRng
Select Case FC
Case False
If rngC.Interior.ColorIndex < xlNone Then
ColCnt = ColCnt + 1
End If
Case True
If rngC.Font.ColorIndex < xlAutomatic Then
ColCnt = ColCnt + 1
End If
End Select
Next
End Function

If you change the color you can press F9 to calculate new.



Hi Claus,

Yep! That is a winner. Thank you very much.

Howard