Solved: pivot table conditional format

[joanna_gr]

Hi all! I need your assistance on the following please. I need to use conditional formating in a pivot table. I need to color only data area and if possible only cells with values and the color to be depended on the value selected in another cell (lets say B2). Is that possible?

[OBP]

joanna, yes this is possible.
You can either use VBA or ordinary conditional formatting.
It really depends on whether your pivot table stays the same size or not.
If it changes size then you might be better using VBA.
If it doesn't change size then do as follows.
Select the range that holds your pivot table.
On the Main Menu select Format>Conditional Formatting
In the first box leave it as "Cell value is".
In the second box select "equal to" from the drop down list.
In the third box, click on the box and then either double click cell B2 or type in the box =$b$2.
Click the "Format" button and click on "patterns", choose your colour and then click "OK".
Click "OK" again and it is done.

[joanna_gr]

I?m afraid it ?s not that easy as a simple conditional format. I have about 10 different colors to use and the pivot table changes according to the color selection. I would prefer to do it through VBA but I don?t have the knowledge to do so and that?s why i?m asking the epxerts !

[OBP]

When you say that the pivot table changes according to the colour selection, does that mean it's size changes or it's content?
Do you intend for cell B2 to hold the Colour you want or a Value?

[joanna_gr]

To be more specific. I have 10 working teams and the data is working hours and each team has it?s own color. Depending on the color I select the values changes (as they refer to different people and different working hours) and in some cases the size changes and not all the teams have the same number of people. I hope this explains a bit the situation.

[geekgirlau]

Joanna, can you post a sample workbook and preferably an example of what you're wanting to achieve?

[joanna_gr]

well here is a small example of my work. As you see there are two groups of people each one of them with its own color. What I need is when I select a color the hours (data column) conditionally changes the color depending on what I have selected. Ideally I would prefer to color only cells with values in them.

[geekgirlau]

This is only set for 2 colours, but you get the idea.

[vba]
Dim rng As Range
Dim varColour As Variant
Dim i As Integer


varColour = Array("Orange", "Yellow")

For i = LBound(varColour) To UBound(varColour)
ActiveSheet.PivotTables("PivotTable1").PivotSelect varColour(i), xlDataAndLabel

For Each rng In Selection
If rng.Column = 1 Or (rng.Column <> 1 And rng.Value <> "") Then
Select Case varColour(i)
Case "Orange": rng.Interior.ColorIndex = 45
Case "Yellow": rng.Interior.ColorIndex = 6
End Select

rng.Interior.Pattern = xlSolid
End If
Next rng
Next i
[/vba]

[joanna_gr]

I see no change ! Why?

[geekgirlau]

Take the colours off your existing pivot table, then run the macro.

[joanna_gr]

Unfortunately is not the result I need. I don?t want to run a macro each time I change a color. I would prefer this to happen automatically. More of this only the first color change color. The data column which I?m interested more does not appear any change

[geekgirlau]

Joanna, the first step is to get the macro changing the colours as you want it to, then we can look at how to trigger the macro.

You need to remove the conditional formatting in the sample you sent me, and remove all fill colours from your pivot table, then run the macro. Does this give you the result you want?

[joanna_gr]

Oh yes ! It works. Super !!! Sorry it was my mistake. Another one thing though. How can I color other columns than the first one? And how can I trigger the macro instead of running it ?

[geekgirlau]

To trigger the macro, add it to the PivotTableUpdate event of the sheet module. I've added some error checking here in case there are colours that have been filtered out.

[vba]Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rng As Range
Dim varColour As Variant
Dim intColour As Integer
Dim i As Integer


varColour = Array("Orange", "Yellow")

For i = LBound(varColour) To UBound(varColour)
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotSelect varColour(i), xlDataAndLabel

' colour may be filtered out of the list, which would cause an error
If Err.Number = 0 Then
For Each rng In Selection
If rng.Column = 1 Or (rng.Column <> 1 And rng.Value <> "") Then
Select Case varColour(i)
Case "Orange": intColour = 45
Case "Yellow": intColour = 6
End Select

rng.Interior.ColorIndex = intColour
rng.Interior.Pattern = xlSolid

If rng.Column = 1 Then
rng.Offset(0, 1).Interior.ColorIndex = intColour
rng.Offset(0, 1).Interior.Pattern = xlSolid
End If

ElseIf rng.Column <> 1 And rng.Value = "" Then
rng.Interior.ColorIndex = xlNone
End If
Next rng
End If
Next i
End Sub[/vba]

[joanna_gr]

thanks for this. Unfortunately I couldn?t make it work. It just turns all cells blank even if I delete the colors first ! Something that I miss again?

[geekgirlau]

If this is part of the Worksheet PivotTableUpdate event for Sheet1, it should run when you refresh data in the pivot table. A quick way to check would be to add a msgbox at the top of the procedure to test that it's being triggered. If you still can't get it working, attached your updated sample file.

[joanna_gr]

Excellent ! Works perfect ! Thank you very much