Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default ColorIndex count function

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default ColorIndex count function

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default ColorIndex count function

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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default ColorIndex count function

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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default ColorIndex count function

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count the Colorindex of a cell with a conditional format Barb Reinhardt Excel Programming 4 April 29th 10 03:59 AM
Colorindex Al_82 Excel Programming 24 September 25th 09 10:25 AM
Can't use COUNT function to count.csv rows? Simon[_2_] Excel Programming 1 July 11th 08 04:59 PM
To reset the Interior.ColorIndex function Daniel Excel Programming 1 June 26th 07 02:12 AM
Public Function to Return ColorIndex Fries Excel Discussion (Misc queries) 2 July 28th 05 09:23 AM


All times are GMT +1. The time now is 09:02 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"