Coloring specific letters in a chart

Posted by Glenn on July 10, 2001 7:52 AM

I have several charts that contain extensive text. I would like to color all W's, X's and Y's that appear in the chart.

Also I would like to bold all instances of specific phrases that appear.

Thank You
Glenn

Posted by Damon Ostrander on July 10, 2001 8:56 AM

Glenn,

You didn't mention what objects contain your text. Is it, for example, the chart's Legend, the axes Titles, or perhaps even TextBoxes that you have overlayed on top of the charts?

Whatever it is, you will simply want to write your VBA code to loop through all the objects and search their Text properties for the phrases, X's, etc. You will need to write a VBA function that identifies exactly which characters in a string match the phrase, and what its starting position and length are. For example, once you know that a "W" occurs in the x-axis title at character position 25, you can set its color:

With Worksheets(1).ChartObjects(1).Axes(xlCategory).AxisTitle

' call routine here to find character position
' of "W" in axis title (.Characters.Text)
' and put it in variable Wstart (in this example
' Wstart = 25)

.Characters(Start:=Wstart, Length:=1).Font.ColorIndex = 3

End With

The same idea applied with setting text to Bold using the .FontStyle = "Bold" property.

I hope this helps.

Damon