Link to home
Start Free TrialLog in
Avatar of Ray Erden
Ray ErdenFlag for United States of America

asked on

Conditional Format

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
Avatar of jkasavan
jkasavan
Flag of United States of America image

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.
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
Avatar of byundt
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

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.
Avatar of Ray Erden

ASKER

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.
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.
Did you clear all the existing Conditional Formatting first?
PGs-Report-PROD-RedAlertsQ281295.xlsm
Followup to byundt:

When I run our code only I17 gets formatted. The whole column I (I13:I6193) gets highlighted but no formatting applies.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of jkasavan
jkasavan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.