It works fine Mary. I think the problem is that your pivot table is not
conditionally formatted, which is what it is testing for.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"maryj" wrote in message
...

Dynamic range:PivotRange=OFFSET('SBT Pivot Data'!$A$9,0,1,COUNTA('SBT
Pivot
Data'!$A$9:$A$65536),COUNTA('SBT Pivot Data'!$B$8:$IV$8))

Function entered: =CFColorCount(PivotRange,10)

Result of Function: #VALUE!

When I enter the name of the dynamic range in the name box, it does select
the correct range of cells so that is working. I also get the #value error
in
the function if I try to use a static named range.

Thank you!!!


--
maryj


"Tom Ogilvy" wrote:

How are you passing the named range to the function. If you have it in
double quotes, then remove the double quotes.

Assume the named range is MyRange, rather than

=CFColorCount("MyRange" ,3)

if should be

=CFColorCount(MyRange ,3)

Otherwise, post a sample formula from the worksheet and the definition of
the named range (from the refers to textbox in the Insert=Name=Define
dialog)

--
Regards,
Tom Ogilvy




"maryj" wrote:

Bob,

You helped me awhile back write some code to count conditional
formatting.
That worked great!!! For awhile....
Now the client has changed it so that the data with the conditonal
formatting is in a pivot table. Everytime the pivot table updates, the
result
of the CFColorCount function changes to #Value. If we manually change
the
range to the exact range, the function will work. I created a dynamic
range
to select the pivot data, but the function won't accept named ranges
for the
argument. Is there any way the function will allow dynamic ranges??? We
really appreciate all of your help. Below is the code you provided us
earlier.

Public Function CFColorCount(rng As Range, _
ciValue, _
Optional text As Boolean = False)
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant

If rng.Areas.Count 1 Then
CFColorCount = "#Too many areas!"
Exit Function
End If

If rng.Cells.Count = 1 Then
CFColorCount = -CLng(CFColorindex(rng, text) = ciValue)
Else
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
CFColorCount = CFColorCount - _
(CLng(CFColorindex(cell, text)) = ciValue)
Next cell
Next row
End If

End Function

Public Function CFColorindex(rng As Range, _
Optional text As Boolean = False)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlNotEqual
CFColorindex = rng.Value < oFC.Formula1
Case xlGreater
CFColorindex = rng.Value oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlLess
CFColorindex = rng.Value < oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value <= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFColorindex = (rng.Value < oFC.Formula1 Or _
rng.Value oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
CFColorindex = rng.Parent.Evaluate(sF1)
End If

If CFColorindex Then
If text Then
If Not IsNull(oFC.Font.ColorIndex) Then
CFColorindex = oFC.Font.ColorIndex
End If
Else
If Not IsNull(oFC.Interior.ColorIndex) Then
CFColorindex = oFC.Interior.ColorIndex
End If
End If
Exit Function
End If
Next oFC
End If 'rng.FormatConditions.Count 0

End Function

--
maryj