How to format a line between two points?

M

Mark Stephens

Hi,

Used to be as easy as recording a macro and seeing how to do it, god knows
why microsoft chose to drop this useful feature... c'est la vie.

I have a line chart and wish to apply a different colour to a portion of the
line according to some vba parameters i have defined.

It would be ideal to be able to do ti without activation the chart, I found
some code posted by John Peltier in December:

With Worksheets("sheet 2?).ChartObjects("chart1?).Chart.SeriesCollection(1)

but this doesn't seem to work giving the error:

Compile error:

Expected: list seperator or )


and highlighting the quote mark after sheet 2 to the bracket after chart1

I have tried messing about with it but can't get it to work.

Anyway, that one aside, I want to say:

For bPointNo = 2303 To 2666

ActiveSheet.ChartObjects("Cht_Comparison").Activate
ActiveChart.SeriesCollection(1).Points(2303).Select
ActiveChart.SeriesCollection(1).Colour = Red

Next bPointNo

The last line is obviously not going to work so I have put it just to show
what I am trying to achieve.

This should be so straightforward, I am pretty disappointed in Microsoft,
why I wonder have they chosen to make excel so un user friendly to vba
users?

Thanks and regards, Mark
 
A

Andy Pope

Hi,

Try this, change the loop values to suit.

With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
.Format.Line.Visible = msoCTrue
.Format.Line.ForeColor.RGB = vbBlue
For bPointNo = 5 To 10
With .Points(bPointNo).Format.Line
.ForeColor.RGB = vbRed
End With
Next
End With

Cheers
Andy
 
A

Andy Pope

Hi,

This sort of thing?

Dim objSeries As Series

Set objSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
objSeries.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent3

Cheers
Andy
 
A

Andy Pope

The msoThemeColorAccent3 type information should appear via intellisense.
If not use the Object browser, F2.
Or the help.

Name Value Description
msoNotThemeColor 0 Specifies no theme color.
msoThemeColorAccent1 5 Specifies the Accent 1 theme color.
msoThemeColorAccent2 6 Specifies the Accent 2 theme color.
msoThemeColorAccent3 7 Specifies the Accent 3 theme color.
msoThemeColorAccent4 8 Specifies the Accent 4 theme color.
msoThemeColorAccent5 9 Specifies the Accent 5 theme color.
msoThemeColorAccent6 10 Specifies the Accent 6 theme color.
msoThemeColorBackground1 14 Specifies the Background 1 theme color.
msoThemeColorBackground2 16 Specifies the Background 2 theme color.
msoThemeColorDark1 1 Specifies the Dark 1 theme color.
msoThemeColorDark2 3 Specifies the Dark 2 theme color.
msoThemeColorFollowedHyperlink 12 Specifies the theme color for a
clicked hyperlink.
msoThemeColorHyperlink 11 Specifies the theme color for a hyperlink.
msoThemeColorLight1 2 Specifies the Light 1 theme color.
msoThemeColorLight2 4 Specifies the Light 2 theme color.
msoThemeColorMixed -2 Specifies a mixed color theme.
msoThemeColorText1 13 Specifies the Text 1 theme color.
msoThemeColorText2 15 Specifies the Text 2 theme color.


Cheers
Andy


mark said:
Hi Andy,

Yes that's it thanks...is there a list of the various presets somewhere do
yoyu know to save me having to find the one I want by trial and error?

Thank as always for your help, kind regards, Mark
 
M

mark Stephens

Thanks Andy, works like a dream... do you know how to formata line using
ther excel 2007 built in presets via vba as the macro won't capture them?

Thanks and regards, mark
 
M

mark Stephens

Hi Andy,

Yes that's it thanks...is there a list of the various presets somewhere do
yoyu know to save me having to find the one I want by trial and error?

Thank as always for your help, kind regards, Mark
 
M

mark Stephens

Thaks a lot for that Andy, appreciate the help, regards, mark

Andy Pope said:
The msoThemeColorAccent3 type information should appear via intellisense.
If not use the Object browser, F2.
Or the help.

Name Value Description
msoNotThemeColor 0 Specifies no theme color.
msoThemeColorAccent1 5 Specifies the Accent 1 theme color.
msoThemeColorAccent2 6 Specifies the Accent 2 theme color.
msoThemeColorAccent3 7 Specifies the Accent 3 theme color.
msoThemeColorAccent4 8 Specifies the Accent 4 theme color.
msoThemeColorAccent5 9 Specifies the Accent 5 theme color.
msoThemeColorAccent6 10 Specifies the Accent 6 theme color.
msoThemeColorBackground1 14 Specifies the Background 1 theme color.
msoThemeColorBackground2 16 Specifies the Background 2 theme color.
msoThemeColorDark1 1 Specifies the Dark 1 theme color.
msoThemeColorDark2 3 Specifies the Dark 2 theme color.
msoThemeColorFollowedHyperlink 12 Specifies the theme color for a clicked
hyperlink.
msoThemeColorHyperlink 11 Specifies the theme color for a hyperlink.
msoThemeColorLight1 2 Specifies the Light 1 theme color.
msoThemeColorLight2 4 Specifies the Light 2 theme color.
msoThemeColorMixed -2 Specifies a mixed color theme.
msoThemeColorText1 13 Specifies the Text 1 theme color.
msoThemeColorText2 15 Specifies the Text 2 theme color.


Cheers
Andy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top