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