Data bar formatting using VBA

[jsuarez199]

Hi Guys,

I have a column of numbers, both positive and negative, ordered by absolute magnitude from top to bottom. I want to put data bars in the column, with the size of the bar according to the absolute values. However, I want the bars to be coloured red when the number is negative and blue when the number is positve. Any ideas? Im using 2007 version. Thanks!!

[Andy Pope]

Don't think you can do that with data bars, in xl2007.

You could construct your own via formula and then use CF to colour font.

[jsuarez199]

This is what I have now...

All I want is the data on the data bar cells to show their sign without altering the size of the bars...

Thanks!

[jsuarez199]

Any ideas anyone?

[JosephP]

as already stated you can't do that in 2007 using data bars. why not use the proposed alternative?

[jsuarez199]

Well I need the bar on the same cell as the Nominal value, as in my sheet. Can this be done with your solution??

[Andy Pope]

whilst you could combine the formula with another one to output value at the end you will have to compromise on bar and number being the same colour and the value appearing after the bar.

[JosephP]

you could use a udf to draw shapes instead