Gradient line add Angle, Color and Position in Line Chart VBA

[sivaprakasam]

Hi,

I am using the excel 2007 and 2013

I have trying the 2-D line chart with recording macro

1st Add the Gradient line (Gradient shops Color is Red" and Position is 0%)
2nd Add the Gradient line (Gradient shops Color is Yellow" and Position is 34%)
3rd Add the Gradient line (Gradient shops Color is Green" and Position is 100%)

Above 3 are not recording the VBA as properly

Kindly provide the VBA code

 

[p45cal]

I suspect you won't be able to do this (except maybe with SendKeys) if Andy Pope's comment here still pertains (msg#2): https://www.excelforum.com/excel-ch...series-as-gradient-line-with-two-colours.html
"not possible as the object model does not expose the required properties for lines"

 

[vletm]

sivaprakasam
Why should You try do that with VBA?
... or do those Gradient stop positions vary?
Could You use chart, which You've done manually?

 

[sivaprakasam]

We are preparing for MIS Report per day more than 20 reports, the Management will be asked difference type of data with chart... So we are trying VBA.... but Tnq for Reply and Help......

 

[p45cal]

At first glance the two charts below are the same:

However, the lower one is coloured using the settings in your msg#1 of this thread.
The upper one has been prepared entirely by a macro.


There are differences; looking more closely, first the lower one:

where you can see each straight-line segment has its own gradation,

but the upper, macro-produced one:

has each segment all one colour (the colour at the level of the rightmost end of each segment).
This doesn't matter too much as your data doesn't change radically from one point to the next.
If it did, things wouldn't be very good. Changing the value at 4:30pm from 99 to 60 (cell B35) shows this difference:
Gradient line. Perfect.


Macro-produced, not so perfect:


Continued in next message because there's a limit of 5 attachments per message…

 

[p45cal]

…in fact that last image needed the chart to be re-created, otherwise the segment colours would remain the same:

which is no good at all.

So bear this in mind when you run the macro in the attached.

The way it works is to add (temporarily) conditional formatting to the cells holding the values of CPU Load, use the colours from those cells to colour the lines in the chart, then clear the conditional formatting of the cells.

All this is really eye-candy. If you have multiple lines on one chart, the colours shown by the gradient formatting will pertain to each line individually, so the red portions will show the maxima of each line which is unlikely to be what you want; at its simplest:

where you can see that at level 3, one line is red, the other green.
It's actually quite easy to put this right using the cell conditional formatting method so that all lines at a given level will be the same colour.