Conditional formatting in pivot table using vba in excel 2007

MPI_IT
MPI_IT used Ask the Experts™
on
I have an excel file and I have created Pivot table(sheet12)
using vba code. I have attached the file with the code.
In sheet1,I have my raw data. I need to do one more formatting
in the pivot table. If the Status field in the raw file,
shows 'Short', I have to make the Qty column yellow in the
pivot table as shown on Sheet12.
Experts, please help. Thanks !
Pivot-test.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
This works without requiring VBA:

1. Highlight the entire F column
2. From the Styles Group on the Home Tab, Select Condtional Formatting
3. Select New Rule
4. Select the Rule Type "Use a formula to determine which cells to format"
5. In the box "Format values where this formula is true" type

=$G1="Short"

6. Click the Format button
7. Select the Fill Tab and choose the yellow color
8. Click OK and click OK again

Commented:
If you DO want to use VBA, then:

Sub HiLiteShort()
'
' HiLiteShort Macro
'

'
    Columns("F:F").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G1=""Short"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Open in new window

Author

Commented:
Thanks for the response. But that is not what I want.
I want to highlight the column in the pivot tabled sheet, based on the status.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

so,

I'd also use your VBA to add the status(short) column.  And after everything is done,

go ahead and cycle through all the cell values, and any cell values that are non-zero and have "short" in the short column apply this code:

With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Open in new window


After that just hide the status(short) column and bob's your uncle!

Author

Commented:
ScriptAddict,
Should I add 'Status' also as data column in the pivottable ?
Could you please modify the existing code to incorporate this ?
Yes,

I'll see if I can get working code based on your sheet and post it when completed.

Sub Pivot_Mod()
 

Application.ScreenUpdating = False
Dim PT As PivotTable
Dim pf As PivotField
Dim today As String
Dim lastR As Integer, lastC As Integer
today = Date


'   Create the Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=Range("A1").CurrentRegion)

'   Add a new sheet for pivot table
    Worksheets.Add

'   Create the Pivot Table

    Set PT = ActiveSheet.PivotTables.Add( _
    PivotCache:=PTCache, _
    TableDestination:=Range("A1"))

'   Add the Fields
    With PT
        .ColumnGrand = False
        .RowGrand = False
        .RowAxisLayout xlTabularRow

        .PivotFields("Customer").Orientation = xlRowField
        .PivotFields("Customer").Position = 1
        .PivotFields("Customer").LayoutForm = xlTabular
        .PivotFields("PlDelDate").Orientation = xlRowField
        .PivotFields("PlDelDate").Position = 2
        .PivotFields("SLine").Orientation = xlRowField
        .PivotFields("SLine").Position = 3
        .PivotFields("SLine").LayoutForm = xlTabular
        .PivotFields("Item").Orientation = xlRowField
        .PivotFields("Item").Position = 4
        .PivotFields("Item").LayoutForm = xlTabular
        .PivotFields("Dates").Orientation = xlColumnField
        .PivotFields("Dates").Position = 1
        .PivotFields("OrdQty").Orientation = xlDataField
        .PivotFields("Count of OrdQty").Function = xlSum
        .PivotFields("Sum of OrdQty").Caption = "Ordered Qty"

    End With
    For Each pf In PT.PivotFields
    'PF.Subtotals(1) = True
    pf.Subtotals(1) = False         ' suppress sub totals for all fields
     Next pf
     
 lastR = ActiveSheet.UsedRange.Rows.Count
 lastC = ActiveSheet.UsedRange.Columns.Count
    'Rotating Dates Column
    Range(Cells(2, 5), Cells(2, lastC)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
  ActiveWorkbook.ShowPivotTableFieldList = False

  Cells.EntireColumn.AutoFit

  Columns("B:B").Select
   Selection.EntireColumn.Hidden = True
  Rows("2:2").RowHeight = 60

   Range(Cells(2, 1), Cells(lastR, lastC)).Select
'    Selection.Font.Bold = True
'    Selection.HorizontalAlignment = xlCenter
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
'This Should add the Status Column into the  table
With PT
        .PivotFields("Status").Orientation = xlRowField
        .PivotFields("Status").Position = 5
        .PivotFields("Status").LayoutForm = xlTabular
End With

'Applies Conditional Formating
 ActiveSheet.Range("F3").Select
   
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$E3=""Short"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).StopIfTrue = False
        With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
    
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(F3))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
   

'This should remove the status column from the table
 Columns("E:E").Select
    Selection.EntireColumn.Hidden = True

Application.ScreenUpdating = True

End Sub

Open in new window

Does that code work for you?

Author

Commented:
Yes, It worked perfectly !!!!
Tried it just now , Thank you so much for the help.
Glad to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial