Bob P - Count conditional formatting

[maryj]

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

 

[Tom Ogilvy]

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)

 

[maryj]

Dynamic rangeivotRange=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!!!

 

[Bob Phillips]

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]

Bob,
The cells in the ptable are using the conditional formatting. The function
will sometimes work but then when the ptable is refreshed and the ptable is
now a different number of rows/columns, one of 2 things happens - either the
values don't update to the correct number and then eventually it changes to
the #Value.

 

[Bob Phillips]

It certainly won't update, as a the refresh doesn't create any event that
the colour calculator picks up on.

You could add VBA to refresh the pivot, and call the a sheet calculate
within that, force a recalculation.

--
HTH

Bob

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

 

[maryj]

Bob,

I hate to ask this, but could I send this new version of the file for you to
look at to see if you can figure out what is wrong?? We have been bumping
into a brick wall with this for the last 2 weeks.

We really appreciate your help!!

 

[Bob Phillips]

Of course. I am out today, but will be able to take a look tomorrow.

Send it to

bob dot ngs at gmail dot com

do the obvious

--
HTH

Bob

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

 

[maryj]

Hi Bob,
I was wondering if you have had a chance to look at my problem file?

Thank you!!
Mary