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?

--
Mahalo,
Island girl Excel user

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.

--
HTH

Bob Phillips

"kjai" wrote in message
...
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?

--
Mahalo,
Island girl Excel user

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

"kjai" wrote in message
...
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?

--
Mahalo,
Island girl Excel user

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

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38530