Dynamic formatting in pivot table

[peperosso]

Hi everyone,
I am a newbie in VBA and I need some help to format my sheet.

I am trying to color the values in my pivot table, and I have some troubles to find a dynamic way to extend the new format when new lines are added to my file.
What I want the macro to do is basically to color each cell in the data fields of the pivot table in the following way:
- if the value of the cell on the left of the referring cell is lower if compared with the referring cell value, paint the referring cell green;
- on the counter, if the value of the cell on the left is higher, paint the referring cell red.

I wrote a short macro that achieves this objective in a flat table:

Firstrow as Integer
Lastrow as Integer
Currentcolumn as Integer
NofColumns as Integer
Dim j As Integer


For j = firstrow To lastrow
Cells(j, Currentcolumn).Select
For i = 1 To NofColumns
ActiveCell.Offset(0, 1).Activate
If ActiveCell.Value > ActiveCell.Offset(0, -1).Value Then
ActiveCell.Interior.ColorIndex = 43
ElseIf ActiveCell.Value < ActiveCell.Offset(0, -1).Value Then
ActiveCell.Interior.ColorIndex = 45
End If
Next i
Next j

The macro works on a flat table that does not change its dimensions. My problem is that I want to apply this formatting to a pivot table where I need to filter the columns, and where the number of rows changes dynamically. I have no idea of how to interact in vba with the values in the pivot table (only with the values, I do not want to format columns' or rows' headers) and how to deal with the volatility of the rows' number.
Do you have any suggestion?

Thanks in advance for your help

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you tried Conditional Formatting with a Formula? It will expand/contract with the pivot table.

 

[peperosso]

Welcome to MrExcel.

Have you tried Conditional Formatting with a Formula? It will expand/contract with the pivot table.

Hi Andrew,thank you for the welcome and for the answer.

I already tried to use conditional formatting with the formula, but I could not find a way to do it sistematically on the entire pivot, without replicating the same formula in any cell. Due to the table dimension, this method is not viable.

 

[Andrew Poulsom]

What version of Excel are you using? If you select a single cell in a Pivot Table and click Conditional Formatting on the Home tab, Excel gives you the option to apply the formatting to al cells in the PivotField. At least that's the case for me in Excel 2010.

 

[peperosso]

What version of Excel are you using? If you select a single cell in a Pivot Table and click Conditional Formatting on the Home tab, Excel gives you the option to apply the formatting to al cells in the PivotField. At least that's the case for me in Excel 2010.

I am using Excel 2013. I just found the option you metioned in your last answer, and that,s exactly what I was searching for!Thank you very much!!