Changing Chart Bar Colours Depending on Condition

[Igwiz]

Hi all,

On a bar chart, I would like to loop through each of the data points and if the value is say <10, then change the colour of the bar to say red. I know how to change the colour in vba by looping through SeriesCollection(1).Points but how do I test the data value it is associated with.
i.e. what is the property associated with Point that returns the datavalue

Many thanks.

Ig

 

[SkipVought]

Hi,

What you need to do is set up your source data with 2 extra columns; one for Value < 10 and the other for Value >= 10

Then in Source Data in the chart, Add these 2 columns and Remove the original Value column.

Set the color for each of these columns to the desired shade.

VOLA
Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

I realise I could do it like that but I was hoping to be able to do it through code to keep the data sheets tidy but thanks anyway.

Ig

 

[SkipVought]

Well, you asked your question in the Office Forum, usually reserved for non VBA solutions. I am a VBA coder, but I OFTEN used spreadsheet solutions when they make sense. This one makes sense, AND it is one that I have used on more than one occasion.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

Sorry, my mistake for posting in the wrong place. I agree with your thoughts but unfortunately am restricted from changing the source spreadsheet layout. I'll try the other forum. Thanks.