need help conditional formatting pivot table

[beckyinDC]

i found a procedure I can effectively use to conditional format rows of a pivot table based upon a condition on the data displayed.
http://peltiertech.com/WordPress/pivot-table-conditional-formatting-with-vba/

Great. the Excel formatting seemed to want to reference addresses that would change, so I liked this as an alternative.

And since I want to apply a format based upon whether count of Failed status is greater than 0...and failed status may not exist at all...I added On error resume next...resolved problem where error would appear if failed wasnt present.

ok- other conditional formatting I want to apply would check if the % metric = 100. PROBLEM: I have 2 metrics displayed (Count and %)...the looping in the example doesnt seem to specify one or the other and it appears to default to the count value. How can I reference the % instead? I think I need to change the For statement?

Sub FormatPT1()
Dim c As Range
On Error Resume Next

With ActiveSheet.PivotTables(1)
' reset default formatting
With .TableRange1
.Font.Bold = False
.Interior.ColorIndex = 0
End With

' apply formatting to each row if some are Passed
For Each c In .PivotFields("Status").PivotItems("Passed").DataRange.Cells
If c.Value > 0 Then
With .TableRange1.rows(c.row - .TableRange1.row + 1)
.Font.Bold = True
.Interior.ColorIndex = 8
End With
End If
Next
' apply formatting to each row if ALL are Passed
For Each c In .PivotFields("Status").PivotItems("Passed").DataRange.Cells
If c.Value = 100 Then
With .TableRange1.rows(c.row - .TableRange1.row + 1)
.Font.Bold = True
.Interior.ColorIndex = 4
End With
End If
Next
...

working in 2007

thanks!

 

[beckyinDC]

still trying to work through this-

my pivot table has rows= Categories, cols= status, and for each intersection I show 2 values= Count and Percent

I can loop through all pivotitems for pivotfield "Status" and process based upon a condition referencing the Count value- that seems to happen by default (so I do NOT have syntax I can adjust easily to reference the Percent value).

I am attempting to adjust the For statement which grabs the objects to loop through

...also tryied another tact of trying to adjust reference of the object within the For/Next loop to point to Percent of the object pulled.

so far its beating me. please help.

 

[beckyinDC]

so i never got an answer, but I found a way to work this:

the pivot table had given me the summarization of data that I wanted, but since it was limiting the formatting I could apply to the rows (based upon my limited understanding at least)...

I used copied the values and took it to a new sheet and turned it into a regular table so that I could then autofilter on any column or set of columns and then apply the formatting I wanted to all displayed rows.

so there's a happy resolution for a Friday...have a good weekend!