Counting Conditional Formats

[Guest]

Aloha,
I'm trying to count the number of cells that have been colored pink (color
index = 38) using a conditional format. I created the following VB function:

Function CCC (myRef As Range, myRange As Range) As Long
Dim Count As Long
Application.Volatile
Count = 0
For Each Cell In myRange
If myRef.Interior.ColorIndex = 38 Then
Count = Count + 1
End If
Next Cell
CCC = Count
End Function

but it doesn't seem to be working with the conditional formating
functionality. How can I adjust this?

 

[Bob Phillips]

Conditional formatting doesn't set the Interior.Colorindex property.

The easiest way is to test the cells against the same formulae that the CF
uses. So if it colours pink for a value of "Pink", test that in the loop.

 

[Henry]

Mahalo,

Function CCC (myRange As Range) As Long
Dim Count As Long
Dim Mycell As Range
Application.Volatile
Count = 0
For Each Mycell In myRange
If Mycell.Interior.ColorIndex = 38 Then
Count = Count + 1
End If
Next Mycell
CCC = Count
End Function

Henry

 

[bhofsetz]

Conditional formatting doesn't actually change the color index of th
cell so your code won't find any cells that meet your condition o
interior.colorindex = 38.

Check out Chip Pearson's site that covers this problem.

http://www.cpearson.com/excel/CFColors.htm

HT