Go Premium for a chance to win a PS4. Enter to Win

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

Use VB to add multiple Numeric Conditional Formats to Pivot

I am working on an Excel 2010 PowerPivot table and need to apply different numeric formatting depending on the value of another cell.  Each measure in the pivot will have 3 possible conditional formats.  If I record the macros to accomplish this, it includes a line like: ExecuteExcel4Macro "(2,1,""0.0%"")"
This line will not execute when re-running the macro.  So I have modified the macro so that it does run, but it does not process properly.   I get the three conditions, but the second and third have no format set.  I feel like I am really close, but have not been able to make any headway on this.  


Sub CondFormat()
'

'Condition 1 Percent
    Selection.NumberFormat = "0.0%"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$K$31=""Pct"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(Selection.FormatConditions.Count).NumberFormat _
    = "0.0%"
    'ExecuteExcel4Macro "(2,1,""0.0%"")"
    
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope  'xlDataField Scope for Measure; xlFieldsScope for Filtered Measure
'Condition 2 Points
    Selection.NumberFormat = "#,##0.0"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$I$31=""Pts"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(Selection.FormatConditions.Count).NumberFormat _
    = "#,##0.0"
   ' ExecuteExcel4Macro "(2,1,""#,##0.0_);(#,##0.0)"")"
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
'Condition 3 Numeric
    Selection.NumberFormat = "#,##0_);(#,##0)"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$I$31=""Num"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(Selection.FormatConditions.Count).NumberFormat _
    = "#,##0_);(#,##0)"
'    ExecuteExcel4Macro "(2,1,""#,##0_);(#,##0)"")"
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope

End Sub

Open in new window

CondFormat.PNG
0
ImageryGrl
Asked:
ImageryGrl
1 Solution
 
ImageryGrlAuthor Commented:
I found the answer!  The key was that I had to explicitly set the priority of each rule.  

im r As Integer, c As Integer, t As Integer
    r = 43  'Row 43 is the first row of the data in the Pivot
    c = 9   ' Colummn 9 "I" is the firt column in the Breathable area

With ActiveSheet
    For c = 9 To 49  'Cycle thru the columns in the pivot table
   
     If .Cells(r - 1, c).Value = "" Then Exit For
    .Cells(r, c).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R31C" & c & "=""Pts"""
    Selection.FormatConditions(Selection.FormatConditions.Count).Priority = 1
    Selection.FormatConditions(Selection.FormatConditions.Count).NumberFormat = "#,##0.0"
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R31C" & c & "=""Pct"""
    Selection.FormatConditions(Selection.FormatConditions.Count).Priority = 2
    Selection.FormatConditions(Selection.FormatConditions.Count).NumberFormat = "0.0%"
    Selection.FormatConditions(2).ScopeType = xlDataFieldScope
    Selection.FormatConditions(2).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R31C" & c & "=""Num"""
    Selection.FormatConditions(Selection.FormatConditions.Count).Priority = 3
    Selection.FormatConditions(Selection.FormatConditions.Count).NumberFormat = "#,##0_);(#,##0)"
    Selection.FormatConditions(3).ScopeType = xlDataFieldScope
    Selection.FormatConditions(3).StopIfTrue = False
   
   Next c
End With
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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