Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Conditional Format

Posted on 2013-05-15
12
Medium Priority
?
415 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 81

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
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!

 
LVL 81

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
 
LVL 81

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 1200 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

636 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