macro for conditional format of sereis on chart

[Ralem]

Hi,

I'll simplify what I need by the following:

I have a range of data (A1:C60) which will be updated on daily basis, I plot range (A1:A60) vs. (B1:B60) on a chart and give my sereis one of 4 colors based on the value of range (C1:C60) which is 1,2,3,and 4.

Now I need a macro please to give color for the point (A1,B1) based on the value of C1 and same for all data range down to bottom and to auto-update the chart whenever the data is updated.

Appreciate support!

[Andy Pope]

Rather than a macro use a chart with 4 series and formula to determine which value is used for each data point/series.
http://peltiertech.com/Excel/Charts/...nalChart1.html

Using Named ranges or List/Table objects will allow for automatic expansion of chart.
http://peltiertech.com/Excel/Charts/Dynamics.html

[Ralem]

Hi Andy,
Thanks for the reply; actually I tried this idea before and it worked very well, but I need a macro this time cause I have more than 100 sereis/points.

[Andy Pope]

Please Login or Register  to view this content.

Adjust colorindex values to suit

[Ralem]

Thanks a lot Andy, I'll try it right right away...

[Ralem]

Hi Andy,

This code is not conditional, is it? If you go back to my question, I need the sereis to be colored based on the values given in the 3d column...
I have 4 cases/groups of colors but I have 60 sereis which will increase evey day, colors to be given based on the sereis group.

[Andy Pope]

Actually my code checks the Y value for conditional colouring

If the value to be checked is in adjecent cell will will need to define a range object to reference it

Please Login or Register  to view this content.

[Ralem]

No need to tell in the code what data is in the range? like (and that's exactly what I need, and for more details I have texts in the range not number) so it'll be:
if the value in the range is "text1" then color sereis with....
if the value in the range is "text2" then color sereis with....
if the value in the range is "text3" then color sereis with....
if the value in the range is "text4" then color sereis with....

[Andy Pope]

Obviously my mindreading powers are failing me.

Post an example workbook so I don't have to second guess.

[Ralem]

Thanks Andy for your patience, sorry I had to be clear and specific from the beginning.

OK, I'm attaching an example now, I have the texts in column D which are grouping my data, when updating the data with adding a point it'll be given a name in column A and one of the texts in column D.
the macro will do the following:

over range A2:D100, the series points will be (Bi,Ci)
Ai is the name of each series, Di is the group of that series

if Di="a" then series is blue circle and show series name to the left
if Di="b" then series is red circle and show series name to the left
if Di="c" then series is black triangle and show series name to the left
if Di="d" then series is blue square and show series name to the left
if Di="e" then series is red square and show series name to the left


I make a series for each point cause I need to show its name on the chart.

Please check the attachment,
Appreciate support!

[Andy Pope]

The attached has both approaches.

Code for multiple single point series and multiple series with multiple points.

[Ralem]

Thanks Andy, this macro is great, but need to add something more if you don't mind:
On the "singlepoint" one, I changed ranges to: Range("A2:A100") and Range("D2:D100") which are blank below row 14; I'll have all this range as blank series on the chart but need this macro to auto run when these blank rows are filled with data.

for the "multipoits" macro; it gives error message, it tells that <Unable to get the HasDataLabel Property of the point class>

[Andy Pope]

When I clear the cells A7:D7 and A10:D14 both sets of codes work as expected.

You need to post example of failing workbook.

[Ralem]

It's OK now, please look at the first part of my last post regarding the "singlepoint" macro.

[Andy Pope]

Use the worksheet change event to run the macro.

Please Login or Register  to view this content.

right click sheet tab and pick View Code. Paste above code.

[Ralem]

But I have many things in my spreadsheet will change when used by users and then in each time the macro will run, can we make it run only when the specific range (A2:A100) is changed.

[Andy Pope]

Did you look at the code? Have you tried it?

The Intersect test restricts updates to changes in the ranges A2:A100 and D2:D100.

[Ralem]

didn't try it, just liked to clarify that point... I'll try it...

[Ralem]

Andy, when I edit only the cell B15 it goes to the macro page and a message pops up with:
Compile Error:
Sub or Function not defined

[Andy Pope]

because I misplet intersect.

Please Login or Register  to view this content.

[Ralem]

Everything is OK now,
You're very kind and patient Andy
Thank you very very very much, you helped me a lot