Multi-Color Characters on Chart Titles on Excel 2007

[roc_on_the_rocks]

On Excel 2003 I was able to set different colors to specific characters on charts titles using a VBA command like this:

ActiveChart.ChartTitle.Select
Selection.Characters(Start:=33, Length:=4).Font.ColorIndex = 3

This was very useful because, imagine a chart containing Temperature data plotted with red lines, I'd make the "Temperature" characters on the Y-axis red too. If "Pressure" was plotted with blue lines, I'd make its description on the Y-axis blue too.

Then our company switched to Excel 2007 those commands crashed and I had to 'comment' them.

Would someone here know how to make it work with Excel 2007?

PS.: This apply to all chart titles (X-Y axes and also main chart title).

Thanks a lot,

 

[pgc01]

Hi

This is an example for the chart title that you can adapt to your needs (tested in excel 2010).

Assuming I have a chart in Sheet1 called "MyChart" with a title with more than 5 characters:

Sub PaintChars()
Dim cht As Chart
Dim chtTitle As ChartTitle
Dim chars As Characters
 
Set cht = Worksheets("Sheet1").ChartObjects("MyChart").Chart
 
Set chtTitle = cht.ChartTitle
With chtTitle
    .Characters(Start:=3, Length:=2).Font.Color = vbRed
End With
End Sub

 

[roc_on_the_rocks]

This is great, thank you so much PGC!

It worked like a charm:

.ChartTitle.Characters(Start:=3, Length:=2).Font.Color = vbRed

 

[pgc01]

I'm glad it helped. Thanks for the feedback.