Solved

VBA Excel 2000 - Conditional Formatting of Pivots

Posted on 2011-02-16
3
442 Views
Last Modified: 2012-06-27
Dear Experts,

Could you please check the attached Code or the Modul1 macro in the attached file, basically it is a simple pivot creation.

My target would be to find some alternative to this line

    Range("E11:E22").Select

so when the pivot is created, I select certain cells for conditional formatting. But the problem is that sometimes I will not know which will be the exact range, as the number of lines can change.

Is there maybe some method which could refer to the pivot name itself so to PivotTable1, and based on that doing Conditional Formatting on maybe RowFields or ColumnFields categories?

thanks,
Sub PivotCreation()

Dim LastRowWithValue As Long
LastRowWithValue = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & LastRowWithValue & "C3").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=Workbooks("PivotVBATemplate.xls").Worksheets("Sheet2").Cells(9, 1)
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
        "Item", ColumnFields:="Area"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Qty")
        .Orientation = xlDataField
        .NumberFormat = "# ##0"
        .Function = xlSum
    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area")
        .PivotItems("West").Visible = False
    End With

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").PivotItems( _
        "South").Position = 1

    Range("E11:E22").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="2000"
    Selection.FormatConditions(1).Interior.ColorIndex = 4
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="2000"
    Selection.FormatConditions(2).Interior.ColorIndex = 3

End Sub

Open in new window

PivotVBATemplate.xls
0
Comment
Question by:csehz
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 34907999
Yes, VBA can be used to select almost any subset of a pivot table. I would do it like this, using a With statement after a PivotSelect statement:

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataOnly, True
    With Selection
      .FormatConditions.Delete
      'The rest of the formatting statements go here, beginning with .FormatConditions
   End With

This will also include the row/column grand total cell at the bottom, but to remove the formatting from that, just do this:

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Column Grand Total'", xlDataOnly, True
    Selection.FormatConditions.Delete

I also noticed that your code is creating a blank worksheet and then placing the pivot table on Sheet2. To avoid this, specify the destination initially rather than setting it in a separate line. That will require activating the sheet in order to keep the "Activesheet" statements that are next. The full code with all of my recommendations is attached.
Sub PivotCreation()

Dim LastRowWithValue As Long
LastRowWithValue = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & LastRowWithValue & "C3").CreatePivotTable TableDestination:=ActiveWorkbook.Worksheets("Sheet2").Range("A9"), TableName:="PivotTable1"
ActiveWorkbook.Worksheets("Sheet2").Activate
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
        "Item", ColumnFields:="Area"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Qty")
        .Orientation = xlDataField
        .NumberFormat = "# ##0"
        .Function = xlSum
    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area")
        .PivotItems("West").Visible = False
    End With

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").PivotItems( _
        "South").Position = 1

    'With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").PivotItems("South").DataRange
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataOnly, True
    With Selection
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
            , Formula1:="2000"
        .FormatConditions(1).Interior.ColorIndex = 4
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="2000"
        .FormatConditions(2).Interior.ColorIndex = 3
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Column Grand Total'", xlDataOnly, True
    Selection.FormatConditions.Delete
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:csehz
ID: 34914583
Telyni19 thanks very much, you even solved the additional sheet problem :-))) Thanks just again for it.

Running your code anyway I am getting a message "Run-time error '450' - Wrong number of arguments or invalid property assignment" at row

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataOnly, True

Can be maybe related that I have Excel 2000 so some version problem?

0
 
LVL 1

Author Closing Comment

by:csehz
ID: 34915215
Telyni19 finally I found it, yes probably some version problem between excels, my one waits that row in this format

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataAndLabel

Your codes works perfectly for me, thanks the help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 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

17 Experts available now in Live!

Get 1:1 Help Now