How to dynamically color a line chart (based on info in a data table)

[Lolo]

Hello,

I want to color a simple line chart with conditional color. Basically I have a data table with 4 columns :
- a column with a year (2014)
- a column with a month (from jan to dec)
- a column with data (from jan to dec)
- a column with info 'REAL' or 'ESTIMATE'. The data is considered as an estimate value when the year month is > current year month.

In my line chart I want to distinguish the 'REAL' values from the 'ESTIMATE', by using 2 colors

For example:
part line for jan and feb is in blue and part line from march to december is in yellow.

I'm able to do it manually by using 2 series, but I need to manually select the right range regarding de period. So each month I would have to change it...

I would have a dynamic solution, where the color change like with a conditional format.

I have tried to use VBA, but if I'm able to focus on a line section, that's all. I'm unable to test what is this section value (which related month or which related info 'ESTIMATE' or 'REAL', in order to specify the right color

If someone can help me...
Thank you

 

[NARAYANK991]

Hi ,

Is it possible to upload your file ?

Narayan

 

[Lolo]

for sure
It is in attached. (it is just a basic example)

I have find a way by adding a column called 'estimate data only', which contains data only if type='estimate', and using 2 series in the chart. (see tab 'DATA2')

but I'm wondering if there is a way to do it only with the original columns, and VBA ? Because If I need to create an extra column at each time, it is perhaps a little bit hard...

 

[Lolo]

I have also found this but it is too complex for me !
https://sites.google.com/site/e90e50fx/home/different-color-for-line-segments-in-excel-charts

 

[NARAYANK991]

Hi ,

See this file.

One point I would like to make is that a line chart is not the correct type of chart for what you wish to do ; a bar chart would be more suitable , and that is what I have used. The values in a line chart pertain to individual points , and the lines are just connections ; thus , if your first two points are actual values , then only those points would be coloured , and not the first two lines. In a bar or column chart , the first two bars or columns would be coloured.

Narayan