Macro to change All Chart Backgrounds to white

[GorD]

My boss as a pet hate for charts that have the standard grey background (as it wastes too much ink when printing). Does anyone have a macro to change the background colour of all charts in a workbook to white

Thanks a lot

 

[AussieRob]

the code to change from grey to white is:

    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With

You will need to use:

for a = 1 to n

next a

to cycle through the graphs, this code will depend on where the graph is ie:new sheet or on the page.

 

[GorD]

I would like it to cover all graphs whether on a separate sheet or part of an existing sheet.

Is that possible?

 

[AussieRob]

okay, first you need to know how many workbooks there are:

a = Activeworkbook.sheets.count

For b = 1 to a

    sheets(b).activate

    'then you need find the number of graphs
    d = ActiveSheet.ChartObjects.Count

    For c = 1 To d

        ActiveSheet.ChartObjects(c).Activate
        ActiveChart.ChartArea.Select
 
        'INSERT THE CODE FOR COLOUR CHANGE HERE

    Next c

next b

Bobs your uncle, your done. (I think)

 

[AussieRob]

You could speed it up a bit if you have lots of sheets by adding an if statement.

if d<>0 then



end if

next b

 

[GorD]

Thanks a lot I'll give it a go

Just to clarify I only need one workbook but to cover all charts but that should do nicely

Thanks again

 

[Alisa]

I have another suggestion which may or may not be helpful- if you are the one generating the charts, you can set up a custom chart type in excel and set that as the default chart type- then, whenever you generate a chart, the background will always be white.

 

[GorD]

In trying to follow Aussie Robs suggestion I came up with this:

Sub GraphBGroundToWhite()
'
' GraphBGroundToWhite Macro
' Macro recorded 01/02/2005 by G Dickson
'
a = ActiveWorkbook.Sheets.Count

For b = 1 To a

    Sheets(b).Activate

    'then you need find the number of graphs
    d = ActiveSheet.ChartObjects.Count

    For c = 1 To d

        ActiveSheet.ChartObjects(c).Activate
        ActiveChart.ChartArea.Select

        With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With

    Next c

Next b
'
End Sub
[code]
Unfortunately, It dosen't change the background colour of the charts. Have I got it wrong? 

Any suggestions?

 

[GorD]

Can anyone help with this

 

[Glaswegian]

Hi GorD

Try this

Sub ChartBackgrounds()
Dim cht As ChartObject
Dim sht As Worksheet
Dim chart As chart

For Each sht In ThisWorkbook.Worksheets
For Each chart In ThisWorkbook.Charts
For Each cht In sht.ChartObjects
chart.PlotArea.Interior.ColorIndex = xlNone
cht.chart.PlotArea.Interior.ColorIndex = xlNone
Next cht
Next chart
Next sht
End Sub


HTH

Regards