Answered by:
How to reference a calculated field of a pivot table to apply conditional formatting
Question

Hi,
I have the following problem. I want to apply a conditional formatting to a calculated field (data field) but I don't know how to do it. If I want to apply a conditional formatting to a standard (not calculated) pivot field (data field) I can do it as follows:
With oPivotTable.PivotFields("Revenue").DataRange .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue [more code here] .FormatConditions(1).ScopeType = xlSelectionScope .FormatConditions(1).ScopeType = xlFieldsScope End With
This doesn't work with a calculated field. As a temporary workaround I decided to use the following approach:
With wksCurrentWorksheet.Range("G12") .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue [more code here] .FormatConditions(1).ScopeType = xlSelectionScope .FormatConditions(1).ScopeType = xlFieldsScope End With
Cell G12 is one (the top most) of the cells in the column of the pivot table with the calculated field. The problem with this approach is that should the pivot table design change the calculated field may no longer be in column G.
Kind regards
Igor M.
 Edited by Igor_M Monday, February 2, 2015 8:53 PM
Monday, February 2, 2015 8:44 PM
Answers

Hi,
I've just found out why I was unable to reference the data range the way I wanted to do it. It's because the calculated field that I wanted to get the range of, with the pivot table layout I had, was not actually a PivotField anymore. When more than one items (pivot fields) are added to the data section of a pivot table, Excel automatically creates a new pivot field (named 'Values') and places it in the column section of the pivot table. All items that are within the data section of a pivot table become PivotItems of the newly created 'Values' PivotField. Hence, to reference the calculated item, for example in order to apply conditional formatting to it, you first need to reference the PivotField and then the item (PivotItem) itself, as in the following code:
With oPivotTable.PivotFields("Values").PivotItem("Variance USD").DataRange.Cells(1) .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue [More Code Here]
.FormatConditions(1).ScopeType = xlSelectionScope
.FormatConditions(1).ScopeType = xlFieldsScope End with
Thursday, February 5, 2015 11:56 AM
All replies

Hi,
I have the following problem. I want to apply a conditional formatting to a calculated field (data field) but I don't know how to do it. If I want to apply a conditional formatting to a standard (not calculated) pivot field (data field) I can do it as follows:
With oPivotTable.PivotFields("Revenue").DataRange .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue [more code here] .FormatConditions(1).ScopeType = xlSelectionScope .FormatConditions(1).ScopeType = xlFieldsScope End With
This doesn't work with a calculated field. As a temporary workaround I decided to use the following approach:
With wksCurrentWorksheet.Range("G12") .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue [more code here] .FormatConditions(1).ScopeType = xlSelectionScope .FormatConditions(1).ScopeType = xlFieldsScope End With
Cell G12 is one (the top most) of the cells in the column of the pivot table with the calculated field. The problem with this approach is that should the pivot table design change the calculated field may no longer be in column G.
Kind regards
Igor M.
Hi Igor,
oPivotTable.PivotFields("Revenue").DataRange
is a read only property. A good workaround is to use the following to find the row and column value of the cell.
Dim rw as long rw = oPivotTable.PivotFields("Revenue").DataRange.row Dim col as long col = oPivotTable.PivotFields("Revenue").DataRange.column With wksCurrentWorksheet.Cells(rw, col) .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue [more code here] .FormatConditions(1).ScopeType = xlSelectionScope .FormatConditions(1).ScopeType = xlFieldsScope End With
Tuesday, February 3, 2015 3:18 PM 
Hi John,
thank you for your reply. It doesn't solve my problem unfortunately.
Maybe I didn't make myself clear.
Let's say we have a pivot table with a field "Cost Centre" and two numeric type fields "Budget" and "Actuals". I place the Cost Centre field in rows and both the Budget and Actual fields to the data field section:
With oPivotTable
.Fields("Cost Centre").Orientation = xlRowField
.AddDataField.PivotFields("Budget"), "Budget USD", xlSum .AddDataField.PivotFields("Actuals"), "Actuals USD", xlSum
End With
I then create a calculated field and place it in the data section of the pivot table:
oPivotTable.CalculatedFields.Add "Variance USD", "='Budget USD''Actuals USD'", True oPivotTable.PivotFields("Variance USD").Orientation = xlDataField
I then want to add conditional formatting to the Budget field, Actuals field and the calculated Variance field. In order to add the conditional formatting to one of the first two (i.e. Budget or Actuals) I can use the DataRange property of these fields, as follows:
With oPivotTable.PivotFields("Budget USD").DataRange .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue With .FormatConditions(1).ColorScaleCriteria(1).FormatColor .Color = 7039480 .TintAndShade = 0 End With .FormatConditions(1).ColorScaleCriteria(2).Value = 50 With .FormatConditions(1).ColorScaleCriteria(2).FormatColor .Color = 8711167 .TintAndShade = 0 End With With .FormatConditions(1).ColorScaleCriteria(3).FormatColor .Color = 8109667 .TintAndShade = 0 End With .FormatConditions(1).ScopeType = xlSelectionScope .FormatConditions(1).ScopeType = xlFieldsScope End With
Calculated fields seem not to have a DataRange property because I get an error at the following line of code:
With oPivotTable.PivotFields("Variance USD").DataRange
So my question really is whether there is such a property of a calculated field that returns a range with data within the pivot table equivalent to the DataRange property available in 'standard' PivotFields that I can use to apply conditional formatting to.
I hope is clear now.
Kind regards,
Igor
Wednesday, February 4, 2015 12:40 PM 
Hi John,
thank you for your reply. It doesn't solve my problem unfortunately.
Maybe I didn't make myself clear.
Let's say we have a pivot table with a field "Cost Centre" and two numeric type fields "Budget" and "Actuals". I place the Cost Centre field in rows and both the Budget and Actual fields to the data field section:
With oPivotTable
.Fields("Cost Centre").Orientation = xlRowField
.AddDataField.PivotFields("Budget"), "Budget USD", xlSum .AddDataField.PivotFields("Actuals"), "Actuals USD", xlSum
End With
I then create a calculated field and place it in the data section of the pivot table:
oPivotTable.CalculatedFields.Add "Variance USD", "='Budget USD''Actuals USD'", True oPivotTable.PivotFields("Variance USD").Orientation = xlDataField
I then want to add conditional formatting to the Budget field, Actuals field and the calculated Variance field. In order to add the conditional formatting to one of the first two (i.e. Budget or Actuals) I can use the DataRange property of these fields, as follows:
With oPivotTable.PivotFields("Budget USD").DataRange .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue With .FormatConditions(1).ColorScaleCriteria(1).FormatColor .Color = 7039480 .TintAndShade = 0 End With .FormatConditions(1).ColorScaleCriteria(2).Value = 50 With .FormatConditions(1).ColorScaleCriteria(2).FormatColor .Color = 8711167 .TintAndShade = 0 End With With .FormatConditions(1).ColorScaleCriteria(3).FormatColor .Color = 8109667 .TintAndShade = 0 End With .FormatConditions(1).ScopeType = xlSelectionScope .FormatConditions(1).ScopeType = xlFieldsScope End With
Calculated fields seem not to have a DataRange property because I get an error at the following line of code:
With oPivotTable.PivotFields("Variance USD").DataRange
So my question really is whether there is such a property of a calculated field that returns a range with data within the pivot table equivalent to the DataRange property available in 'standard' PivotFields that I can use to apply conditional formatting to.
I hope is clear now.
Kind regards,
Igor
Thanks for the clarification. Okay, I am assuming your calculated field is adjacent to the added field (Budget Field". Then we can cheat a little bit. :grin:Dim col as long col = oPivotTable.PivotFields("budget").DataRange.column + 1 ' assuming it is adjacent Dim firstRow as long firstRow = oPivotTable.PivotFields("budget").DataRange.row ' first row remains unchanged Dim lastRow As Long 'for last row in column lastRow = Cells(Rows.Count, col).End(xlUp).Row
Wednesday, February 4, 2015 2:41 PM 
Hi,
I've just found out why I was unable to reference the data range the way I wanted to do it. It's because the calculated field that I wanted to get the range of, with the pivot table layout I had, was not actually a PivotField anymore. When more than one items (pivot fields) are added to the data section of a pivot table, Excel automatically creates a new pivot field (named 'Values') and places it in the column section of the pivot table. All items that are within the data section of a pivot table become PivotItems of the newly created 'Values' PivotField. Hence, to reference the calculated item, for example in order to apply conditional formatting to it, you first need to reference the PivotField and then the item (PivotItem) itself, as in the following code:
With oPivotTable.PivotFields("Values").PivotItem("Variance USD").DataRange.Cells(1) .FormatConditions.AddColorScale ColorScaleType:=3 .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue [More Code Here]
.FormatConditions(1).ScopeType = xlSelectionScope
.FormatConditions(1).ScopeType = xlFieldsScope End with
Thursday, February 5, 2015 11:56 AM