Formatting Pivot Tables in VBA (errors and others)

WildfireS3

New Member
Joined
Nov 20, 2009
Messages
11
Hi there

I've created a pivot table, and would like to colour the various colums according to title, but I am having a few issues trying to get it the code to work completely.

Here is what I have so far:

Sub Colour()
On Error Resume Next
ActiveSheet.PivotTables("Actions_Aging").PivotSelect "'a More than 3 months unitl due'", _
xlDataAndLabel
With Selection.Interior
.ColorIndex = 43
.Pattern = xlSolid
End With

On Error Resume Next
ActiveSheet.PivotTables("Actions_Aging").PivotSelect "'b 2-3 months unitl due'", _
xlDataAndLabel
With Selection.Interior
.ColorIndex = 43
.Pattern = xlSolid
End With

End Sub

This goes on for about 5 more columns in the pivot table, each being coloured different colours.

But...

I get an error if Excel cannot find one of the columns in the middle of the code (it's a monthly report so not all column will appear each month), and if the missing column is at the end of the code it colours the last column found the same colour as specified in the last bit of the code.

e.g

if it finds a,b,c,d,e, but there is code for a-g, then e will be the same colour as g, not e.

Thanks in advance.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

tfaulkes

Board Regular
Joined
Jun 4, 2009
Messages
74
I believe what is occuring is you have stated "On Error Resume Next" so when it hits and error the code continues to execute line by line. So if your code does not error on "E" then processes until the end of the procedure, it will get to "G" and error but continue with the With Selection and change the colorindex of the selection that is already highlighted by previous code. You could add additional logic to check the name prior to changing colorindex then create a Case statement that could process the colorindex based upon your logic.
 

WildfireS3

New Member
Joined
Nov 20, 2009
Messages
11
Thanks. I get the idea of the if statement, but what sort of logic can I use? Ideally I'd just like it to bypass the specific colouring and move on to the next one.

Is there not an error check I could use?

Sorry I'm very new to VBA having only used it for about 4 days properly.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,123,330
Messages
5,601,000
Members
414,419
Latest member
JRDunya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top