Conditional formatting of charts.

I have a column chat that plots some positive and negative values. I would like for the positive columns to be one color and for the negative once a different color. Normally i would acomplish this by having 2 separate data sets one for the positive values and one for the negative once and then setting the color for them separately. My data however is pretty large at this point so i started to use a pivot table and i can either plot the pos values or the neg or all and all the columns are the same color. Does anyone know of a way to acomplish this?

Hi

If I understood well you have a chart type Column with one only series, that has positive and negative values. You want the positive values in one colour and the negative values in another.

If having just one series is really more convenient to you, I believe you have to run a vba snippet after the chart is drawn.

It will be a small vba routine that:_
- loops through the points of the series
- for each point paints the column in a different colour depending on the value being positive or negative

If you need help to write it, post details, like is the chart a sheet or is it embedded in a worksheet, the name of the chart if it's a sheet or of the chart object if it's embedded, if the chart really has only one series, etc.

HTH
lecxe

Hi

This code paints the positive values in green and the negatives in red.
Try:

Please Login or Register  to view this content.

HTH
lecxe

Hi

The code should work for a chart sheet.

However, you can try an equivalent syntax, that rules out the confusion.

Replace

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

You should only get a a "subscript out of range error" if the name of the sheet is missplelled like "Chart 1" (with a space before the "1") instead of "Chart1".

HTH
lecxe

Hi

I'm sorry that it's not working for you. I don't know why.
Both versions work for me with no problem.

Can you please run ans analyse this code?
It may help understanding the problem:

Please Login or Register  to view this content.

Hope this helps to understand the problem
lecxe

Found the solution
I used this line

Please Login or Register  to view this content.

Thank you lecxe for your help, much apreciated

Hi

I'm glad it works now.

However, the line you use means it's not a chart sheet, it's a chart embedded in a worksheet, more precisely it's the first chart object in worksheet Table.

Anyway the important thing is that your problem is solved.

Cheers
lecxe