Solved

Can I use VBA to format every Pivotfield in a table that contains header P021 with a specific background colour

Posted on 2010-09-14
25
633 Views
Last Modified: 2012-05-10
Hi everybody,

I have a Pivottable, that includes a number of columns with sales figures for different years. for each year I have an account in the Pivotcolumns called P021 that I would like to give a backgroundcolour that are for instance green.

But I am not able to give that column a background colour other than by formatting the individual cells.

How can I colour the whole Pivotcolumn in a green colour by VBA?

best regards

Jørgen
0
Comment
Question by:Jorgen
  • 11
  • 7
  • 4
  • +1
25 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33677021
Jørgen,

It would be useful to see some sample data and/or a sample file. EE now allows you to directly upload files
to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible
to fully and permanently delete it. Therefore, be very careful about posting proprietary, confidential, or
other sensitive information. If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types. If your file type does not match
those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run
by people connected to EE.

Patrick
0
 
LVL 33

Expert Comment

by:Norie
ID: 33685337
Jorgen

Are you sure you need VBA?

If this was 'normal' data, ie not a pivot table, you could use conditional formatting and I can't
think of any reason why you couldn't use it with a pivot table.

The formulas might not be a little more complicated as usual mind you.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33688517
Hi Imnorie and Patrick,

I need the VBA to format the data as I need the process to be automated and I will not be responsible of the process apart from implementing it.

The users that will be updating and controlling this will be finance people with very little experience in both Pivot tables and conditional formatting. And they will not know much about how to upload data either.

Patrick I will upload a file as soon as I get 10 minutes, but I have to modify data, as they are customer confidential, and therefore can not be uploaded.

Jørgen
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33689501
Hi Patrick

I have hereby uploaded the file. I had to change a little bit in titles, so the issue is now that all basic figures (not totals) in Columns that includes the header 10/11 YTD should have the bachground colour White. that all basic figures (not totals) in Columns that includes the header 09/10 YTD should have the bachground colour Blue. that all basic figures (not totals) in Columns that includes the header 10/11 Budget should have the bachground colour Green.  And I Need a border round all Volumes areas for instance the Pivotfields included in column E and F

Jørgen
Customer-Analysis-Example-file.xls
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33691051
I still don't see why you can't use Conditional Formatting for this.I did the following, successfully:1) Selected E4:IV65536 on the sheet with the PT2) Added a formula-based CF rule using the following formula:=AND(E4<>"",COUNTIF(E$3,"*10/11 YTD*"),COUNTIF($A4:$D4,"*total*")=0)
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33692159
Hi Patrick,

I can partly solve the problem in this way, But as (which I should have told you) I am working in Excel 2003 I only have 3 conditions to set, I can get my colouring, but not the borders, and I will only colour cells that have a content, not cells that are empty but part of the area of the pivot table.

For the border I can make a border round the individual cells, but not around the whole area of the pivot table. In this case it could for instance be the Area E3:F95.

This has been my reasoning for trying to set the background colour for the whole area of the Pivottable.

Hopefully this gives more insight of the requirement.

Jørgen
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33692314
Sorry, I had misread your last comment.The borders will be a problem, but the coloring can still be done with Conditional Formatting.  I used these CF formulas:=AND(E4<>"",COUNTIF(E$3,"*Budget 10/11*"),COUNTIF($A4:$D4,"*total*")=0)    (for green)=AND(E4<>"",COUNTIF(E$3,"*09/10 YTD*"),COUNTIF($A4:$D4,"*total*")=0)     (for blue)No rule is needed for the white coloring, because your default background is already white.BTW, you do not have any headings that include "10/11 Budget".  You do have some with "Budget 10/11", and thus that is what I used.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33693178
Jørgen

Can you explain all the formatting you want to apply?

As has been said borders can be a problem, conditional formatting doesn't allow certain types of formatting for some reason.

What that reason is I can't recall, it's lost in the mists of time I think.

Do you still want to see if it's possible using code?

I think it might be and might not be as difficult as I first thought, though I'll probably need to take a look at your file to see if that's right.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33693208
The only bit that looks tricky to me for code is avoiding the totals rows. Other than that you just use the pivotfield's data range property.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33693538
Rory

I've still not had a look at the file but that's the sort of thing I was thinking of using.

As you probably know a pivot table has quite a few range properties for different parts.

I think using them and perhaps a little bit of resizing/offsetting the formatting could be done in code.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33698552
Hi Rory,

My hope was that I could use the Pivotfields data range property, but I seem not to be able to find the right code for it.

I want to do this in VBA, as this template is to be the standard of a number of reporting pivottables and that I should not use a lot of time updating all other peoples pivottables.

I have uploaded a new file, where I in column G and H have showed how my columns should look ideally both with columns and background fill.

I can generate the code for the conditionally formatting, that Patrick suggested on the conditionally formatting, but I still do not feel that my report has a look that the users will find brilliant.

I am only working as a consultant for the company, and they spend hours formatting their pivottables - and they actually use pivottables to an extend that I have never experienced before without having much knowledge of how to optimize.

Jørgen
Customer-Analysis-Example1-file.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33699172
Why are the rows coloured green different from the rows coloured yellow? And why do the two gross sales columns have borders around them? (that doesn't seem to match your question)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33699329
Is this the sort of thing you had in mind?



Sub FormatPivot()

    Dim pt As PivotTable

    Dim pf As PivotField

    Dim strField As String

    Set pt = ActiveSheet.PivotTables(1)

    

    Application.ScreenUpdating = False

    

    For Each pf In pt.DataFields

        strField = UCase$(pf.Caption)

        If InStr(1, strField, "BUDGET 10/11", vbBinaryCompare) > 0 Then

            FormatRangeColour pf.DataRange, 6 ' yellow

        ElseIf InStr(1, strField, "YTD 10/11", vbBinaryCompare) > 0 Then

            FormatRangeColour pf.DataRange, 4 ' green

        End If

    Next pf

    

    Application.ScreenUpdating = True



End Sub

Sub FormatRangeColour(rng As Range, lngCI As Long)

    Dim rngCell As Range

    rng.Interior.ColorIndex = lngCI

    For Each rngCell In rng

        With rngCell.PivotCell

            If .PivotCellType = xlPivotCellBlankCell Then

                rngCell.Interior.ColorIndex = xlColorIndexNone

            ElseIf .RowItems.Count < .Parent.RowFields.Count Then

                rngCell.Interior.ColorIndex = xlColorIndexNone

            End If

        End With

    Next rngCell

End Sub

Open in new window

0
 
LVL 4

Author Comment

by:Jorgen
ID: 33699659
Hi Rory,

Yes it is absolutely something like this, that I Need. The only issue on the colouring side is that for Totals it removes the colouring from the total columns, which is acceptable, but if it could be solved, it would be perfect.

The colours are there to show which data groups that are related to each other, for instance that actual volume for the year is related to actual sales for the year and to the KPI's calculated for actual data for the year.
The borders is created to show a specific group of data together for instance volumes Year to Date and the comparison to Volumes Budget Year to date and Volumes Last Year to date.

Jørgen
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33699749
>>"The only issue on the colouring side is that for Totals it removes the colouring from the total columns"
I'm not following this - where specifically should there be colour but isn't?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33699989
For instance in G50 , or in any other of the columns, where the header is Strategic Brands Total and that is updated with the VBA code.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33699997
Beforehand there was a background colour, but that is removed.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33700014
I specifically added that in because you hadn't coloured them in the sample! Before we carry on can you provide a sample that shows EXACTLY which rows should be coloured and which shouldn't?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33701184
Hi Rory,

The lines that are coloured is fine, But from the automaticly format that was generated through the format report function in the Pivottoolbar some rows are coloured in advance. These lines are in this specific case line 50, 71, 73 and 93, but that can vary depending on the source. It is actually the totals of the highest hierarki (or the first rowfield chosen).

And if possible the formatting from the format report function should be kept if possible.

Jørgen

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33701229
Sounds hideous, but I'd still like to see a sample that categorically shows exactly what you want as the end result.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33701671
The ideal file will look like this. This also includes the borders, that I wrote about.

Jørgen
Customer-Analysis-Example-file.xls
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33701901
How about this:



Sub FormatPivot()

    Dim pt As PivotTable

    Dim pf As PivotField

    Dim strField As String

    Dim n As Long, lngRowCount As Long

    

    Set pt = ActiveSheet.PivotTables(1)

    lngRowCount = pt.DataBodyRange.Rows.Count

    Application.ScreenUpdating = False

    

    For Each pf In pt.DataFields

        strField = UCase$(pf.Caption)

        If InStr(1, strField, "BUDGET 10/11", vbBinaryCompare) > 0 Then

            FormatRangeColour pf.DataRange, 6 ' yellow

        ElseIf InStr(1, strField, "YTD 10/11", vbBinaryCompare) > 0 Then

            FormatRangeColour pf.DataRange, 4 ' green

        ElseIf InStr(1, strField, "YTD 09/10", vbBinaryCompare) > 0 Then

            FormatRangeColour pf.DataRange, 3 ' red

        End If

    Next pf

    

    For n = 1 To pt.DataFields.Count Step 2

        pt.DataFields(n).LabelRange.Cells(1).Resize(lngRowCount + 1, 2).BorderAround xlContinuous, xlMedium

    Next n

    Application.ScreenUpdating = True



End Sub

Sub FormatRangeColour(rng As Range, lngCI As Long)

    Dim rngCell As Range

    rng.Interior.ColorIndex = lngCI

    For Each rngCell In rng

        With rngCell.PivotCell

            If .PivotCellType = xlPivotCellBlankCell Then

                rngCell.Interior.ColorIndex = xlColorIndexNone

            ElseIf .RowItems.Count = 1 Then

                rngCell.Interior.ColorIndex = rngCell.Offset(0, -1).Interior.ColorIndex

            ElseIf .RowItems.Count < .Parent.RowFields.Count Then

                rngCell.Interior.ColorIndex = xlColorIndexNone

            End If

        End With

    Next rngCell

End Sub

Open in new window

0
 
LVL 4

Author Closing Comment

by:Jorgen
ID: 33702846
Hi Rory,

Thanks for a great job done. This code will help both me and my customer in the future.

It also taught me about a few things about VBA for pivot tables, that I have not found in any books regarding VBA and Pivot tables.

Jørgen
0
 
LVL 33

Expert Comment

by:Norie
ID: 33703173
Jørgen

Is that really how the data needs to be presented?

I don't know why but it seems a bit cumbersome.

Note I'm not saying don't use whatever format you want/need, just that it might be worth considering a few changes.

Perhaps just something like adding a page field and resizing some of the columns.

PS Just tried Rory's code but it appears Excel 2000 is missing a few pivot objects.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 33704101
Hi Imnorie

No that is not how the data is presented but I already had the code for formatting the different fields as either rows or fields. I also have the formats, that shows the different kind of numberformats.

I only had to figure out the formats for the border and the backgroundcolouring. I have not put it in the excelfile. as I wrote to you guys that it was customer confidential data ,that I had to change before uploading it to the EE universe.

Actually when I have finished the template, a file will be taken from a datawarehouse,and my excelfile will be updated with the latest actual data. Then the pivottable will be updated and all the formats will be added. Finally the file is sent to the relevant users.

But I was glad for all the comments given even if the VBA code Rory send was the solotion I was looking for.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

759 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

23 Experts available now in Live!

Get 1:1 Help Now