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