locked
How to reference a calculated field of a pivot table to apply conditional formatting RRS feed

  • 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






    • Marked as answer by Igor_M Thursday, February 5, 2015 11:57 AM
    • Edited by Igor_M Thursday, February 5, 2015 11:59 AM
    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






    • Marked as answer by Igor_M Thursday, February 5, 2015 11:57 AM
    • Edited by Igor_M Thursday, February 5, 2015 11:59 AM
    Thursday, February 5, 2015 11:56 AM