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
647 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 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