Automate chart series colors in PPT

[jmartell]

I have searched for days to try to find information on how to attach a color to a series.

For example... I have a chart linked from Excel to PPT and I want my charts to alway use Red for "Tomato",Yellow for "Banana" etc. The series will change based on their rank in current sales. How do I alway keep the colors attached to the series no matter where it ranks?

Secondly,
Is there a way to automate text in PPT text boxes that read from Excel? I update the %change of sales and would like to use a text box rather than a chart title but want it to link to PPT based on the cell value in Excel.

Thanks for any help.

PS, I am a beginner so please go easy on the tech language! Thanks much.

J9

[Zack Barresse]

Hello J,

As far as the chart in PPT, I'm assuming (correct me where I'm wrong) that you inserted an Object -> Microsoft Excel Chart -> From File -> Linked. If this is the case, follow these steps:

Within the Linked Excel file*, if Chart Title exists, proceed to step 4
1) Right click the chart, select Chart Options...
2) In the Chart Title, type any data (e.g. 'Hi', 'etc', 'blah')
3) Press Ok
4) Select the Chart Title (if not selected already)
5) Click in the formula bar
6) Type ='Sheet1!$A$2 or your equivelant linked cell
7) Press Enter

* You can also right click your Object and select Linked Worksheet Object -> Open/Edit (whichever works for you).
It is imperative that you follow the steps exactly, click by click.

As for the color (picture attached below), you must do this from within Excel also. What you want to do is, when you have your chart created (I'm assuming that this is a bar chart, so that is what I'll talk about) you'll need to double click any one of the bars associated with your different 'foods' or series. You are presented with the Foramt Data Series dialog box. Change the Area color (right hand side) to the color desired for that particular Series. Once you have them all changed to the color you want, considering that both files (Excel and PPT) are both open, the changes will automatically be reflected in you PPT Linked Chart Object.

Picture attached below. I can attach the files also if you'd like, although you may have to link those up again to see the changes.

...

[shades]

Take a look at this. It allows you to link the Excel data range (or chart) to PowerPoint. Just right-click the image in PPT and select Update Link.

VBA code to Paste Link into PPT

Then, consider the following for adjusting the chart colors in Excel prior to using the above:

Sub ColorCompChart()
	Dim i As Long
	Dim myComp As String
	Dim myRow As Integer
	Dim myCol As Integer
 
	If ActiveChart Is Nothing Then
		InputBox "Select Chart"
		Exit Sub
	End If
	With ActiveChart
	With ActiveChart.SeriesCollection(1)
	myRow = InputBox("What is row number")
	myCol = InputBox("What is column number?")
		For i = 1 To 7
			myComp = ActiveSheet.Cells(myRow - 1 + i, myCol).Value
			Select Case myComp
				Case "CompA"
					.Points(i).Interior.ColorIndex = 33
				Case "CompB"
					.Points(i).Interior.ColorIndex = 40
				Case "CompC"
					.Points(i).Interior.ColorIndex = 6
				Case "CompD"
					.Points(i).Interior.ColorIndex = 3
				Case "CompE"
					.Points(i).Interior.ColorIndex = 26
				Case "CompF"
					.Points(i).Interior.ColorIndex = 1
				Case "Other"
					.Points(i).Interior.ColorIndex = 17
			End Select
		Next i
	 End With
	 End With
 
End Sub

Note: the two InputBoxes ask for numbers of the upper left cell of the data set (not the title row). Thus, if you have column headings in row 1, and data that begins in D2, the Row number would be 2, and the column number would be 4. If the heading is in H6, then the row number is 7, and column number is 8.