• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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


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



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......:):):)


Well-Known Member
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…
Last edited:
  • Like
Reactions: Hui


Well-Known Member
…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.


  • Like
Reactions: Hui