Colour chart line segments

[N1GHTEYES]

Hi all,

I have a macro which colours the points of an x,y series according to a 3rd data set. That's a bit obscure so I'll explain.

Imagine you have a bunch of x,y data, and a corresponding 3rd set, say the number of the data channel which recorded the point. The macro allows me to select the range holding the data channel information and then colours the chart points according to it. So, if there were, say, 5 channels, each of the points would have 1 of 5 colours depending on which channel it came from. I hope that's clear.

Now for the problem. As I say, I can colour the points OK, but I'd like to be able to also colour the line segment adjacent to the point too. Obviously, there will be one fewer lines than points, so one of the points will not have a corresponding line to colour, but I can live with that. I've looked at the Series object properties but can't find how to address the line segments. Does anyone know if this is possible, & if so, how?

 

[SkipVought]

Hi,


Make a column for each color. Each column is s different series. Make sure that empty cells to not plot in the Chart Options.

Skip,

_[red][/red]

 

[N1GHTEYES]

Thanks Skip

Yes, I could do that, and would with some data, but in other circumstances, the macro I described is very handy, and leads to a much less busy spreadsheet. So, if it is possible, I really would like to be able to individually colour the line segments as well as the points. I just can't see how to address them in code.

Tony

 

[SkipVought]

Line segments, you can't, AFAIK.

Points, you can.

Look at the SeriesCollection Collection, Points Collection, DataLables.

Post your code when you run into problems.

Skip,

_[red][/red]

 

[N1GHTEYES]

If my code was giving me any probs (with this issue) then I'd post it, but the macro, as it stands, is working fine with colouring the points. I just wanted to add line segment colouring too.

However, previous posts would seem to indicate that if you don't know of a way then there probably isn't one. I guess I'll make do without.

Thanks anyway.

 

[SkipVought]

Each Series line can be individually shaded.

Just make additional series.

I do this often with Horizontal Bar Charts designed as Gantt Chart. I can "Change" the color of a bar, simply by controlling the source data ranges
[tt]
1 10 0
2 15 0
[/tt]
becomes
[tt]
1 0 10
2 0 15
[/tt]
and VOLA, the colors change!

Skip,

_[red][/red]

 

[N1GHTEYES]

To do what I was after, I'd effectively have to add a new series for every point. That might get a tad messy.

Never mind, it's no major problem - coloured points is really all I need. Besides, having a single consistent line colour at least allows me to display a sensible legend.

 

[SkipVought]

Again....

POINT Objects can be programatically changed. There is a Points Collection.

But you're not asking about POINTS. Your question was about line SEGMENTS.

A LINE SERIES is an object. The ENTIRE line can be changed, but not a LINE SEGMENT. My example refers to a simulating a LINE SEGMENT with a separate SERIES.

For instance, if you wanted to have ONE LINE with three different colors, you would need THREE DIFFERENT SERIES.

Skip,

_[red][/red]

 

[N1GHTEYES]

Skip,

I realise that, but I'm usually dealing with thousands of points and a variable number of values in the third data set by which I want the points (& segments) coloured.

That would be a fair number of series. It would also mean a significant mod (well, a re-writing) of my current code, which just changes the point colours of the selected series, and works pretty well.

To do what you're suggesting would mean changing the code to create a new series every time the colouring data changed. I could do that, but, for the moment at least - until I come across a case where it would make a big difference to the usability, it just isn't worth the effort. I do appreciate the suggestion - it hadn't occurred to me - but I think I'll keep it in reserve. Thanks anyway.

Tony

 

[SkipVought]

If you care to post a representative sample of your source data and describe how you want it plotted, I'll give it a look.

Skip,

_[red][/red]

 

[N1GHTEYES]

Skip

Sorry about the delay, I wasn't rudely ignoring your offer - I've just been off-site for a few days.

Thanks for the offer, but, I think that, on balance it is probably best for the moment to stick to the points colouring. The idea of colouring the line segments actually came as a request from a colleague who uses my add-in (containing the point-colouring macro). After you guys confirmed that there was no simple way of addresssing the segments, I had a look at his workbook and reconfigured it to display his data as seperate lines (as someone above suggested). I still think that there would be some cases where it would be beneficial to colour segments, if it were possible, but, for now, it's not worth my, or anyone else's effort.

 

[SkipVought]

So, is this thread completed?

Skip,

_[red][/red]

 

[N1GHTEYES]

Completed, finished, finalised, terminated and possibly closed.

This thread is dead. It is no more. It is an ex-thread. It wouldn't go Voomph if you stuck 40,000 volts through it.

Tony