Sub Format_Report() Dim objShape As InlineShape Dim title As String 'prevent the application from updating until macro is done Application.ScreenUpdating = False 'loop through each inline object in the document For Each objShape In ActiveDocument.InlineShapes 'if the object is a chart, format and color the chart If objShape.HasChart Then 'show title for debugging 'MsgBox (title) 'format the chart to have axis data labels and a legend If objShape.chart.ChartType = xlColumnStacked _ Or objShape.chart.ChartType = xlBarStacked Then objShape.chart.ApplyLayout (1) ElseIf objShape.chart.ChartType = xlPie Then objShape.chart.ApplyLayout (6) For i = 1 To objShape.chart.SeriesCollection.Count objShape.chart.SeriesCollection(i).DataLabels.ShowValue = True Next i ElseIf objShape.chart.ChartType = xlBarClustered Then objShape.chart.ApplyLayout (1) objShape.chart.Legend.Delete ElseIf objShape.chart.ChartType = xlColumnClustered Then objShape.chart.ApplyLayout (1) End If 'color the chart series differently, depending on the chart title title = objShape.chart.ChartTitle.Text If title = "Annual Energy Costs" Then 'PEA 'Electricity objShape.chart.SeriesCollection(1).Interior.Color = RGB(255, 255, 0) 'Gas objShape.chart.SeriesCollection(2).Interior.Color = RGB(153, 76, 0) 'District Heating objShape.chart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0) 'District Cooling objShape.chart.SeriesCollection(4).Interior.Color = RGB(0, 102, 204) 'end use bar charts ElseIf title = "Annual Energy Costs" _ Or title = "Annual Utility Costs by End Use" _ Or title = "EDA Baseline Monthly Peak Electric Demand" _ Or title = "EDA Baseline Monthly Electricity Consumption" _ Or title = "EDA Baseline Monthly Natural Gas Consumption" Then 'PEA, FEA 'Heating objShape.chart.SeriesCollection(1).Interior.Color = RGB(255, 0, 0) 'Cooling objShape.chart.SeriesCollection(2).Interior.Color = RGB(0, 0, 255) 'Lighting Interior objShape.chart.SeriesCollection(3).Interior.Color = RGB(255, 255, 0) 'Lighting Exterior objShape.chart.SeriesCollection(4).Interior.Color = RGB(255, 178, 102) 'Equipment Interior objShape.chart.SeriesCollection(5).Interior.Color = RGB(0, 204, 0) 'Equipment Exterior objShape.chart.SeriesCollection(6).Interior.Color = RGB(0, 153, 0) 'Fans objShape.chart.SeriesCollection(7).Interior.Color = RGB(255, 102, 255) 'Pumps objShape.chart.SeriesCollection(8).Interior.Color = RGB(178, 102, 255) 'Heat Rejection objShape.chart.SeriesCollection(9).Interior.Color = RGB(153, 204, 255) 'Humidification objShape.chart.SeriesCollection(10).Interior.Color = RGB(204, 0, 102) 'Heat Recovery objShape.chart.SeriesCollection(11).Interior.Color = RGB(153, 0, 0) 'Water Systems objShape.chart.SeriesCollection(12).Interior.Color = RGB(255, 153, 51) 'Refrigeration objShape.chart.SeriesCollection(13).Interior.Color = RGB(51, 255, 255) 'Generators objShape.chart.SeriesCollection(14).Interior.Color = RGB(192, 192, 192) 'end use pie charts ElseIf title = "EDA Baseline Annual Utility Cost by End Use" Then 'PEA, FEA 'Heating objShape.chart.SeriesCollection(1).Points(1).Interior.Color = RGB(255, 0, 0) 'Cooling objShape.chart.SeriesCollection(1).Points(2).Interior.Color = RGB(0, 0, 255) 'Lighting Interior objShape.chart.SeriesCollection(1).Points(3).Interior.Color = RGB(255, 255, 0) 'Lighting Exterior objShape.chart.SeriesCollection(1).Points(4).Interior.Color = RGB(255, 178, 102) 'Equipment Interior objShape.chart.SeriesCollection(1).Points(5).Interior.Color = RGB(0, 204, 0) 'Equipment Exterior objShape.chart.SeriesCollection(1).Points(6).Interior.Color = RGB(0, 153, 0) 'Fans objShape.chart.SeriesCollection(1).Points(7).Interior.Color = RGB(255, 102, 255) 'Pumps objShape.chart.SeriesCollection(1).Points(8).Interior.Color = RGB(178, 102, 255) 'Heat Rejection objShape.chart.SeriesCollection(1).Points(9).Interior.Color = RGB(153, 204, 255) 'Humidification objShape.chart.SeriesCollection(1).Points(10).Interior.Color = RGB(204, 0, 102) 'Heat Recovery objShape.chart.SeriesCollection(1).Points(11).Interior.Color = RGB(153, 0, 0) 'Water Systems objShape.chart.SeriesCollection(1).Points(12).Interior.Color = RGB(255, 153, 51) 'Refrigeration objShape.chart.SeriesCollection(1).Points(13).Interior.Color = RGB(51, 255, 255) 'Generators objShape.chart.SeriesCollection(1).Points(14).Interior.Color = RGB(192, 192, 192) ElseIf title = "Whole-Building EUI" Then 'FEA For i = 1 To objShape.chart.SeriesCollection(1).Points.Count objShape.chart.SeriesCollection(1).Points(i).Interior.Color = RGB(128, 128, 128) Next i ElseIf title = "Peak Electric Demand" Then 'FEA 'Electricity Demand objShape.chart.SeriesCollection(1).Interior.Color = RGB(255, 255, 0) 'District Cooling Demand objShape.chart.SeriesCollection(2).Interior.Color = RGB(0, 102, 204) ElseIf title = "Electricity Consumption" Then 'FEA 'Electricity Consumption objShape.chart.SeriesCollection(1).Interior.Color = RGB(255, 255, 0) 'District Cooling Consumption objShape.chart.SeriesCollection(2).Interior.Color = RGB(0, 102, 204) ElseIf title = "Natural Gas Consumption" Then 'FEA 'Gas Consumption objShape.chart.SeriesCollection(1).Interior.Color = RGB(153, 76, 0) 'District Heating Consumption objShape.chart.SeriesCollection(2).Interior.Color = RGB(255, 0, 0) ElseIf title = "Natural Gas Consumption" Then 'FEA 'Gas Consumption objShape.chart.SeriesCollection(1).Interior.Color = RGB(153, 76, 0) 'District Heating Consumption objShape.chart.SeriesCollection(2).Interior.Color = RGB(255, 0, 0) ElseIf title = "EDA Baseline Annual Utility Cost by Fuel Type" Then 'FEA 'Electricity Consumption Charge objShape.chart.SeriesCollection(1).Points(1).Interior.Color = RGB(255, 255, 0) 'Electricity Demand Charge objShape.chart.SeriesCollection(1).Points(2).Interior.Color = RGB(255, 128, 0) 'Gas objShape.chart.SeriesCollection(1).Points(3).Interior.Color = RGB(153, 76, 0) 'Other Energy objShape.chart.SeriesCollection(1).Points(4).Interior.Color = RGB(204, 153, 255) 'District Cooling objShape.chart.SeriesCollection(1).Points(5).Interior.Color = RGB(0, 102, 204) 'District Heating objShape.chart.SeriesCollection(1).Points(6).Interior.Color = RGB(255, 0, 0) End If 'end looking through chart titles End If 'end if chart Next 'next inline object 're-enable screen updating Application.ScreenUpdating = True End Sub