Formatting Pivot Tables in VBA (errors and others)

[WildfireS3]

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.

 

[tfaulkes]

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]

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