Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109206

Re: ColorIndex count function

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

ColorIndex count function "L. Howard" <lhkittle@comcast.net> - 2016-08-24 01:06 -0700
  Re: ColorIndex count function Claus Busch <claus_busch@t-online.de> - 2016-08-24 10:29 +0200
    Re: ColorIndex count function "L. Howard" <lhkittle@comcast.net> - 2016-08-24 03:21 -0700
      Re: ColorIndex count function Claus Busch <claus_busch@t-online.de> - 2016-08-24 12:30 +0200
        Re: ColorIndex count function "L. Howard" <lhkittle@comcast.net> - 2016-08-24 03:58 -0700

csiph-web