Pivot Table VBA Conditional Format on PivotField 1

[TornadoWilkes]

Hi

I've been searching for days all over the web and then remembered you guys. I wondered if anyone could help me with this please?

I have a PivotTable that tracks whether or not projects are completed on time. Against each project number are 5 columns (pivot fields not the data section) that describe the status of "milestones," i.e. did phase 1 complete "On time", "Late", "Early" etc. based on what is written in this field I'd like the field background to format in Red, Amber or Green.

To make matters more complex, the status is part of a string e.g. it may say "Late: 24 days, was due on 21/1/2010" or "Task completed on time: Finished on 3/1/2010."

Could anyone help me get them to colour Green Amber and Red please?

I'm pretty new to VBA in pivots so any help would be appreciated?

Cheers
Ian

Ian
Developer
Tarmac

 

[SkipVought]

Please state the actual logic assiciated with each color.

Please post a sample of the PT data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TornadoWilkes]

Cheers Skip

I'll have to work out how to attach on this site, but here's a Tab delimited sample version of the data that can be pasted into Excel and converted to columns..


Project Number Project Name Project Decription Project Manager Country 1 - Project Mandate 2 - Initiation 3 - Go/No Go 4 - Delivery 5 - Post Implementation Location Benefits
1 Sample Project Name Sample Project Description Mr Project Manager UK Complete: 10d early 1/3/10 Late completion: 30d 20/02 OK: on time 43d to go 28/04 OK: on time 70d to go 25/05 OK: on time 260d to go 01/12 £100
1 Sample Project Name Sample Project Description Mr Project Manager France Complete: 10d early 1/3/10 Late completion: 30d 20/02 OK: on time 43d to go 28/04 OK: on time 70d to go 25/05 OK: on time 260d to go 01/12 £200
1 Sample Project Name Sample Project Description Mr Project Manager Brazil Complete: 10d early 1/3/10 Late completion: 30d 20/02 OK: on time 43d to go 28/04 OK: on time 70d to go 25/05 OK: on time 260d to go 01/12 "£1,000"
1 Sample Project Name Sample Project Description Mr Project Manager USA Complete: 10d early 1/3/10 Late completion: 30d 20/02 OK: on time 43d to go 28/04 OK: on time 70d to go 25/05 OK: on time 260d to go 01/12 "£1,500"
2 Sample Project Name 2 Sample Project Description 2 Mrs Project Manager UK OK: on time 43d to go 28/05 Risk: 1d late 15/04 30d to mitigate OK: on time 1d to go 16/3 Risk: 4d 25/3 10d to mitigate OK: on time 200d to go 03/10 "£5,000"
2 Sample Project Name 2 Sample Project Description 2 Mrs Project Manager France OK: on time 43d to go 28/05 Risk: 1d late 15/04 30d to mitigate OK: on time 1d to go 16/3 Risk: 4d 25/3 10d to mitigate OK: on time 200d to go 03/10 "£3,000"
2 Sample Project Name 2 Sample Project Description 2 Mrs Project Manager Brazil OK: on time 43d to go 28/05 Risk: 1d late 15/04 30d to mitigate OK: on time 1d to go 16/3 Risk: 4d 25/3 10d to mitigate OK: on time 200d to go 03/10 "£1,999"
2 Sample Project Name 2 Sample Project Description 2 Mrs Project Manager USA OK: on time 43d to go 28/05 Risk: 1d late 15/04 30d to mitigate OK: on time 1d to go 16/3 Risk: 4d 25/3 10d to mitigate OK: on time 200d to go 03/10 "£50,000"


The logic is:

If the 5 milestones contains within the string:

Late then Red
(Complete or OK) then Green
Risk then Amber

that individual pivot field turns the appropriate colour.

Ian
Developer
Tarmac

 

[SkipVought]

Use, for instance
[tt]
Formula Is: =SEARCH("LATE",CellRef)>0
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TornadoWilkes]

Cheers Skip

I guess I'm struggling knowing what to use as the CellRef.
I'm attempting to use the pivot as a cube, and in the past have put conditional formatting into each field allowing the pivot to be reorientated or new dimensions added and the formatting sticks after the refresh.

Likewise with this pivot I'm trying to allow the user to insert dimensions without an issue so am trying to avoid formatting directly into a cell or a column.

I did previously try:
=SEARCH("LATE",CELL("Celladdress"))>0 in the conditional format for the field, but it doesn't appear to work.

Which is why I was considering the VBA route as an alternative?

Ian
Developer

 

[SkipVought]

I guess I'm struggling knowing what to use as the CellRef.

Select ALL the cells you want to CF with the same logic.

The CellRef is the TOP LEFT CELL in the selection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TornadoWilkes]

Skip

You're a superstar, I would never have guessed that's where you reference.

It now works a treat apart from the OK or Complete for Green.

I've got as a formula:
=OR(SEARCH("OK",E11)>0,SEARCH("Complete",E11)>0)

Which doesn't work on OK's although:

=OR(SEARCH("OK",E11)>0,SEARCH("OK",E11)>0)

Does for some strange reason.....

Any ideas?

Ian
Developer

 

[SkipVought]

Try this
[tt]
=OR(NOT(ISERROR(SEARCH("OK",E11))),NOT(ISERROR(SEARCH("Complete",E11))))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TornadoWilkes]

Lol

Again, works a treat... for a newbie here I'm in awe lol

Ian
Developer

 

[SkipVought]

Thanks!

Let me tell you how I discovered what was wrong.
[tt]
1. COPY the CF formula

2. Edit an empty cell and PASTE in the Formula Bar.
result =OR(SEARCH("OK",E11)>0,SEARCH("Complete",E11)>0)
Hit ENTER.
I have ok in E11 and complete in E12

3. Back in the Formula Bar...
select...
=OR(SEARCH("OK",E11)>0,[highlight]SEARCH("Complete",E11)[/highlight]>0)

4. Hit F9 -- this EVALUATES what is highlighted.

5. Observe the results and then hit ESC - This is VERY IMPORTANT as it returns the formula, otherwise the value remains in the expression.

This is where I found that the search on "Complete" returns an error.
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}