Format Chart Bars with code?
 

I have data laid out in 3 columns... date in the first, a number in the
second,and a color (Green, Red, or Blue) in the next. I have a chart with
floating bars and would like them to be the color of the color in column
three. Can I do that with code? Is there a better way?

Format Chart Bars with code?
 

The conditional chart technique on this web page works without any VBA:

http://peltiertech.com/Excel/Charts/...nalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John" wrote in message
...
I have data laid out in 3 columns... date in the first, a number in the
second,and a color (Green, Red, or Blue) in the next. I have a chart with
floating bars and would like them to be the color of the color in column
three. Can I do that with code? Is there a better way?

Format Chart Bars with code?
 

yea, I was hoping you wouldn't say that... I have made my own candle charts
with multiple series for up/down open/close etc... using that technique. I
was hoping there was an easier way then reworking all those series

"Jon Peltier" wrote:

The conditional chart technique on this web page works without any VBA:

http://peltiertech.com/Excel/Charts/...nalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John" wrote in message
...
I have data laid out in 3 columns... date in the first, a number in the
second,and a color (Green, Red, or Blue) in the next. I have a chart with
floating bars and would like them to be the color of the color in column
three. Can I do that with code? Is there a better way?

Format Chart Bars with code?
 

Would this general example work? It assumes the colors are in column C.
Create a bar chart, activate it, and run the code.

Sub ColorBars()

Dim Rng As Range
Dim Color As Integer

Color = Range("C1").Interior.ColorIndex
Set Pts = ActiveChart.SeriesCollection(1).Points(1)
Pts.Interior.ColorIndex = Color

Color = Range("C2").Interior.ColorIndex
Set Pts = ActiveChart.SeriesCollection(1).Points(2)
Pts.Interior.ColorIndex = Color

Color = Range("C3").Interior.ColorIndex
Set Pts = ActiveChart.SeriesCollection(1).Points(3)
Pts.Interior.ColorIndex = Color

End Sub

-- Can be modified to work more efficiently by looping, etc.


--
John


"John" wrote:

yea, I was hoping you wouldn't say that... I have made my own candle charts
with multiple series for up/down open/close etc... using that technique. I
was hoping there was an easier way then reworking all those series

"Jon Peltier" wrote:

The conditional chart technique on this web page works without any VBA:

http://peltiertech.com/Excel/Charts/...nalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John" wrote in message
...
I have data laid out in 3 columns... date in the first, a number in the
second,and a color (Green, Red, or Blue) in the next. I have a chart with
floating bars and would like them to be the color of the color in column
three. Can I do that with code? Is there a better way?