• 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

    Hui...

  • 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

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



66558
 

Attachments

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

Well-Known Member
At first glance the two charts below are the same:
66601
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:
66602
where you can see each straight-line segment has its own gradation,

but the upper, macro-produced one:
66603
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.
66604

Macro-produced, not so perfect:
66606

Continued in next message because there's a limit of 5 attachments per message…
 
Last edited:
  • Like
Reactions: Hui

p45cal

Well-Known Member
…in fact that last image needed the chart to be re-created, otherwise the segment colours would remain the same:
66612
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:
66613
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.
 

Attachments

  • Like
Reactions: Hui
Top