locked
Conditional Formatting in a Pivot Table RRS feed

  • Question

  • Hi

    I am seeting up a pivot table in VBA and am adding data fields - see example below.

    With pvtUtilisation.PivotFields("Nov-17")
          .Orientation = xlDataField
          .Function = xlSum
          .Position = 1
          .NumberFormat = "0.00"
          .Name = "Nov-17 "
     End With

    I want to add some conditional formating so that :

    if the value is < 0.5 then fill cell with vbyellow

    if  value > 1 and <= 1.2 fillcell  with  vbgreen

    if value > 1.2 fill cell with vbred

    i have tried to set up formatconditions and apply these but i cannot get it to  work. I can do this if it is a non-pivot - but cannot get to work within the pivot - ideally looking for some code to add to the With statement above to make this work.

    Note - this is an example with Nov-17 but I will have 12 columns going from Nov 17 to Oct 18 and need to apply formatting to them all.

    Can you help please?

    thanks

    Peter

    Wednesday, November 22, 2017 4:00 PM

Answers

  • Hi py,

    I think key point is how to get the range of the pivot field. Where is the pivot table "Nov-17"? Is it in Values field?

    If so, you could try to get the range via PivotField.DataRange Property and then add conditional formatting to this range. You could try to refer to below code and adjust it for your need.

     Dim rng As Range
        Set rng = ActiveSheet.PivotTables(1).DataBodyRange
        rng.FormatConditions.Delete
        rng.Select
        addressStr = rng.Cells(1, 1).Address(False, False)
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=0.5"
        With Selection.FormatConditions(1).Interior
            .Color = vbYellow
        End With
    
        
        Selection.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=(" & addressStr & ">1)*(" & addressStr & "<=1.3)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Color = vbGreen
        End With
        
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=1.2"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Color = vbRed
        End With
    

    If not, please share a simply file so we could use it for trying to reproduce your issue.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by py1 Thursday, November 23, 2017 10:37 AM
    Thursday, November 23, 2017 4:51 AM

All replies

  • Hi py,

    I think key point is how to get the range of the pivot field. Where is the pivot table "Nov-17"? Is it in Values field?

    If so, you could try to get the range via PivotField.DataRange Property and then add conditional formatting to this range. You could try to refer to below code and adjust it for your need.

     Dim rng As Range
        Set rng = ActiveSheet.PivotTables(1).DataBodyRange
        rng.FormatConditions.Delete
        rng.Select
        addressStr = rng.Cells(1, 1).Address(False, False)
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=0.5"
        With Selection.FormatConditions(1).Interior
            .Color = vbYellow
        End With
    
        
        Selection.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=(" & addressStr & ">1)*(" & addressStr & "<=1.3)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Color = vbGreen
        End With
        
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=1.2"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .Color = vbRed
        End With
    

    If not, please share a simply file so we could use it for trying to reproduce your issue.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by py1 Thursday, November 23, 2017 10:37 AM
    Thursday, November 23, 2017 4:51 AM
  • Hi Terry

    Thanks very much for this input - it gave me enough clues to help me work out the result I needed.

    much appreciated.

    kind regards,

    Peter

    Thursday, November 23, 2017 10:37 AM