Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

apply conditional formating from one cell to another in a pivot table

Hi,
This is kind of hard to explain, so I've attached the workbook.
IN the raw data I've created a column (Final Percent of Target) with values 1,2,3 (good, moderate, bad) based on whether or not the values are some percentage of their targets. When placed in a pivot table, I conditionally format this column and call it performance to  green , yellow and red.  All is well.
However to save space, I would like to color code the actual values the same as the
performance column, and then perhaps hide this column.
I could envision how to do this outside of a pivot table, but not within the pivot table.

Any work-arounds, solutions, help greatly appreciated.


interim-beds-reporting-all-table.xlsx
0
Diaphanosoma
Asked:
Diaphanosoma
  • 2
1 Solution
 
Arno KosterCommented:
It will involve some work setting it up, but it can be done like this :

select conditional formatting from the menu ribbon
apply to cells B8 & C8 ( =$B$8:$C$8)
select 'use formula'
use this formula : =(D8=0)
set colorless background formatting

repeat for D8=1 (green formatting), D8=2 (yellow formatting), D8=3 (red formatting)
repeat for all rows
0
 
Arno KosterCommented:
You can automate this with a macro like
Sub apply_formatting()
Dim pt As PivotTable
Dim row As Range

    Set pt = PivotTables(2)
    For Each r In pt.DataBodyRange.Rows
        '-- clear existing conditional formatting
        r.FormatConditions.Delete
        '-- attention : row.range("A1:B1") relates to cells Bx:Cx !!
        With r.Cells(1).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=0)")
            .Interior.Color = xlNone
            .StopIfTrue = False
        End With
        With r.Cells(2).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=0)")
            .Interior.Color = xlNone
            .StopIfTrue = False
        End With
        With r.Cells(1).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=1)")
            .Interior.Color = ColorConstants.vbGreen
            .StopIfTrue = False
        End With
        With r.Cells(2).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=1)")
            .Interior.Color = ColorConstants.vbGreen
            .StopIfTrue = False
        End With
        With r.Cells(1).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=2)")
            .Interior.Color = ColorConstants.vbYellow
            .StopIfTrue = False
        End With
        With r.Cells(2).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=2)")
            .Interior.Color = ColorConstants.vbYellow
            .StopIfTrue = False
        End With
        With r.Cells(1).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=3)")
            .Interior.Color = ColorConstants.vbRed
            .StopIfTrue = False
        End With
        With r.Cells(2).FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Cells(1).Address & ";0;2)=3)")
            .Interior.Color = ColorConstants.vbRed
            .StopIfTrue = False
        End With
    
        '-- attention : row.range("D1:E1") relates to cells Ex:Fx !!
        With r.Range("D1:E1").FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Range("D1").Address & ";0;2)=0)")
            .Interior.Color = xlNone
        End With
        With r.Range("D1:E1").FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Range("D1").Address & ";0;2)=1)")
            .Interior.Color = ColorConstants.vbGreen
        End With
        With r.Range("D1:E1").FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Range("D1").Address & ";0;2)=2)")
            .Interior.Color = ColorConstants.vbYellow
        End With
        With r.Range("D1:E1").FormatConditions.Add(Type:=xlExpression, Formula1:="=(OFFSET(" & r.Range("D1").Address & ";0;2)=3)")
            .Interior.Color = ColorConstants.vbRed
        End With
    
    Next r
    
End Sub

Open in new window


there are multiple ways of doing this, somehow excel messed up the formulae so i had to circumvent this using a displacement routine (OFFSET). You can do this cell-by-cell (upper part), or set the conditional formatting on a range (lower part). please note that the ranges are relative to the pivot table body and thus do not correpsond to regular cell ranges !
0
 
DiaphanosomaAuthor Commented:
Sorry I haven't been able to respond...

I will give this a try shortly. Looks good.

Thanks,

Bruce
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now