Change chart color bars VBA code assistance

**BGiffin2004** · 12-14-2011, 10:53 AM

VBAChartColorChange.xlsm
I am trying some VBA code that is supposed to change the colors of a chart depending on what colors the datasheet contains in column "A". (eg. "A1" is green then the color bar in the chart for the data pertaining to "A1" should be green also.)

It is not working. I get an error code: Object variable or With block variable not set." I have searched the internet but am not able to find a similar situation to mine. And I still have a hard time searching this site - I couldn't find anything about an error 91.

(I have attached my workbook - and threw the code in below in case that is what someone likes to work with also.) Thanks - and I will reply to any answers; and let the person know if got a solution out of thier reply.

Please Login or Register  to view this content.

**StephenR** · 12-14-2011, 11:02 AM

Strange, it didn't work for me at first, then I added this line:

Please Login or Register  to view this content.

and it worked, then I commented out that line and it worked so confused as to what is going on. See if that line makes it work for you.

Btw your error was due to your having to select the chart before you run the macro. If it is not selected it will error as code refers to activechart.

**BGiffin2004** · 12-14-2011, 11:51 AM

StephenR,

I worked out a solution myself. But the solution does not get all the colors right in the chart - 4 of the colors are gray, and I can't change them by changing the colors in the sample range. (I attach a copy of my new worksheet.)

But alas! I will certainly try what you said before I go trying to fix my newest solution. I will try it right now in fact. - Will let you know soonest how it goes. I am fairly new, so it might be a little while (I also like to work it this way and that sometimes too.)

VBAChartColorChange(5).xlsm

**BGiffin2004** · 12-14-2011, 12:19 PM

StephenR,

I did what you said and it works just like my solution I found (see my 2nd post with attchment VBAChartColorChange(5).xlsm). I am part of the way there.
Your solution (as was mine in post #2), both have dull colors (not bright like the data set) for 6 of the 10 color bars. Then 4 of the color bars are all the same medium gray color.

Is that what you get when you ran the macro? Maybe it is my version of Excel2010?

Oh, and I couldn't even add the line you suggested. I got an error 91: "Object variable or With block variable not set". What I did do was to "select the chart", and then run the macro.

**StephenR** · 12-14-2011, 12:39 PM

Did you put the line after your current colourindex line? I tried in Excel 2003 before, but do get the problem you describe in 2007, but it seems to work if you use the Color property instead of ColorIndex.

**BGiffin2004** · 12-14-2011, 01:00 PM

StephenR,

I figured it out with some research on the net. It seems that using ".ColorIndex" was for Excel2003. In Excel2007 & 2010 it will not work properly with that. I found that if I substitute ".Color" then the macro runs with all the colors coming out well. Thanks very much for your help and support. I will mark this as "Solved". - And give you good marks. Have a great holiday season!