Solved

Conditional Format

Posted on 2013-05-15
12
347 Views
Last Modified: 2013-05-15
On the attached file I have a pivot table where I need to apply conditional formatting.  What I am trying to do is that for example if there is a value 1 in the cell AB17 then the cell I17 cell should turn red.  I achieved this on another pivot but for whatever reason I just can not obtain the same results on this one. Format should be applied to the entire pivot table starting from I13 cell all the way down to W8000.  Along with your solution if you could also describe your steps I would truly appreciate it as I need to put this in a macro. You can find a macro that worked on my other pivot table except on this file its range has been modified for this pivot. Macro name ConditionalFormat.  Not sure why it does not work here at all.

Thank you very much in advance
PGs-Report-PROD-RedAlerts-.xlsm
0
Comment
Question by:trusxlsol
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 2

Expert Comment

by:jkasavan
ID: 39170100
Does it have to be macro command? If you set up conditional formatting to a Dynamic Range setting then it would work.

I created a new conditional format rule:

Apply Rule To: =$I$13:$I$6193 (you can make this a dynamic range with the OFFSET COUNTA structure)

Select

Use a formula to determine what cells to format

In the box Format values where this formula is true type

=VALUE(AB13)=1

Then set the fill to red on the format selection screen.
0
 

Expert Comment

by:Spence53
ID: 39170114
Maybe I am missing something but this seems very easy.  I am using Excel 2010.
- Simply highlight cell I17, and select Home->Conditional Formatting -> New Rule.
- Select "Use a formula to determine which cells to format"
- Enter the formula "=$AB$17" by pointing at the target cell, and then edit this rule by changing it to say "=AB17>0" (or something similar.  This will display red for any non-zero value in cell AB17, which is a relative address.)
- Select Format color of Red (As per your request).
- OK
- Use the Format Painter to copy this conditional Format to any/all other cells that you wish.
Copy-of-PGs-Report-PROD-RedAlert.xlsm
0
 
LVL 80

Expert Comment

by:byundt
ID: 39170162
You need to clear the existing rules for Conditional Formatting from the worksheet before beginning.

Assuming that you want to apply Conditional Formatting only to column I when column AB equals 1:
1)  Select cell I13, then open Conditional Formatting menu item with New Rule with Formula criteria
2)  Apply the Conditional Formatting to range I13:I6193. You get an error message if the Conditional Formatting tries to extend beyond the PivotTable
3)  Use this criteria formula:
=$AB13=1                 the reference to row 13 will update as required
4)  Click the Format button, then select the red highlight color
Sub CFcolumnI()
    With ActiveSheet.Range("$I$13:$I$6193")     'All of PivotTable column I data
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$AB13=1"
        .FormatConditions(1).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions(1).ScopeType = xlSelectionScope
    End With
End Sub

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
ID: 39170176
Darned recorded macro didn't work. This one does:
Sub CFcolumnI()
    With ActiveSheet.Range("$I$13:$I$6193")     'All of PivotTable column I data
        .Cells(1, 1).Activate
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$AB13=1"
        .FormatConditions(1).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = True
        .FormatConditions(1).ScopeType = xlSelectionScope
        .Cells(1, 1).Copy
        .PasteSpecial xlPasteFormats
    End With
End Sub

Open in new window

If you want to check whether the Conditional Formatting is working, look at cell I411.
0
 

Author Comment

by:trusxlsol
ID: 39170213
To byundt:

After I applied the desired format as described above to the cell I13 ( with AB13=1 criteria and obviously for cell I14 with AB14=1 criteria and so forth).  After copying the format using the format painter all the way down to I6293 all cells are painted to red regardless of the criteria.  This is my problem!!!  I also entered your code and ran it but did not work.
Could you format on the file and resend the working file if possible? I need to formayt also via macro due to the reaon that after refreshing the pivot it loses the existing format so I have to run format macro following the refresh.Thanks.
0
 

Author Comment

by:trusxlsol
ID: 39170217
To jkasavan:

Yes it has to be done through a macro because after refreshing the pivot table the existing format disappears and I want to apply the same format via macro instead of recreating it each time pivot gets refreshed.  I do this on another pivot and it works just fine I am so very puzzled why it does not work here.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 80

Expert Comment

by:byundt
ID: 39170221
Did you clear all the existing Conditional Formatting first?
PGs-Report-PROD-RedAlertsQ281295.xlsm
0
 

Author Comment

by:trusxlsol
ID: 39170225
Followup to byundt:

When I run our code only I17 gets formatted. The whole column I (I13:I6193) gets highlighted but no formatting applies.
0
 
LVL 2

Expert Comment

by:jkasavan
ID: 39170226
In that situation I have had success by assigning the conditional format to the ENTIRE column - $I:$I and then the conditional format is still in effect even after a table refresh.
0
 

Author Comment

by:trusxlsol
ID: 39170232
To jkasavan:

I have been really grappling with this very simple sounding conditional formatting problem and very frustrated with it. If you are saying that you had a success with it the way you say would yo please apply to my file and send me the working solution?  Thank you.
0
 
LVL 2

Accepted Solution

by:
jkasavan earned 300 total points
ID: 39170248
OK - here it is. Only changes was to apply the formula to $I:$I and then make the formula "=AB1=1".
Copy-of-PGs-Report-PROD-RedAlert.xlsm
0
 

Author Closing Comment

by:trusxlsol
ID: 39170280
Thanks for the help my problem has been resolved. What helped me were VALUE(AB13)=1 syntax and putting I:I in the apply to box of the formatting to keep the format after refreshing the pivot.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now