You don't really need VBA:
http://peltiertech.com/Excel/Charts/...nalChart1.html

If you really want to use VBA, here is a technique:
http://peltiertech.com/WordPress/vba...arts-by-value/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Qaspec" wrote in message
...
I have a bar chart that I would like to format the color depending on the
value in the data range.

If the value of the data point is 1 I'd like to format the bar in the
chart
red, if the value is 2 then blue, if the value is 3 then gold and if the
value is 4 then green.

I read some earlier posts and I feel more comfotabel using some vba in
order
to complete this.

I did try to use a function posted in an answer to another question but I
get the following error at this line " Set Pts =
ActiveChart.SeriesCollection(1).Points(Cnt)": Run Time Error 91

Here is the code:

Sub ColorBars()

Application.ScreenUpdating = False

Dim Rng As Range
Dim Cnt As Integer

Cnt = 1

For Each Rng In Range("G66:G77")
Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
If Rng.Value = "1" Then
Pts.Interior.ColorIndex = 24
ElseIf Rng.Value = "3" Then
Pts.Interior.ColorIndex = 15
ElseIf Rng.Value = "4" Then
Pts.Interior.ColorIndex = 19
End If
Cnt = Cnt + 1
Next Rng
End Sub