Macro doesn't change fill colour only line colour

[RuairiO]

Hi all,

I have a macro set up to change the colour of the triangle in my graph/scatter plot to indicate the project task status. The colour changes based on the selection from the drop down list in S31.

Only problem is it is only changing the Line colour, but not the Fill colour... I can't seem to figure it out especially since I took it from the macro recorder.

See attached my example excel, but below a code snippet. I would really appreciate if someone could have a quick look.

Please Login or Register  to view this content.

[6StringJazzer]

The macro recorder is OK but the code it generates always needs review.

The line that changes ForeColor works. For some reason, the next two lines reverse the effect. I do not know why setting the line to 0 transparency and solid would do that.

Also, you don't need to active and select things to work on them. This code works correctly:

Please Login or Register  to view this content.

[Bernie Deitrick]

Don't use a macro - use one graph line per color by splitting your data into columns - I set up the Red values for you in the attached.

[6StringJazzer]

I just answered the question but Bernie Deitrick solved the problem

[RuairiO]

Thank you both so much! Worked like a charm.

[RuairiO]

Thanks very much for showing a way to do this without a macro btw. I had actually orginally asked this in another thread, but had gotten no answer and so went the macro route. I need to check this a little deeper. Thanks a lot!

[RuairiO]

Thanks a lot. This really helps. I find it touch to target objects in charts with macros, but this looks pretty simkple actually so thanksa lot.

In the end I went with this for now, unless work doesn't want macros

Then I added the below to make it dynamic. Thanks a lot. I attached the updated sheet in case anyone finds it handy.

Please Login or Register  to view this content.

[Bernie Deitrick]

Or I just hate dealing with graphs in VBA

[6StringJazzer]

Thanks a lot. This really helps.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time. --6StringJazzer

[RuairiO]

Just to let you know I went back to your solution and took some time to understand it. I now just updated my graph with your table and now that I understand it I actually find this much easier than using the macros. This is really a great solution plus I learned a lot about conditional formatting of graphs Thanks so much!!!

[Bernie Deitrick]

And you don't have to deal with graphs in VBA! (My favorite part of that LOL)