Conditional Format Bar Chart

[Qaspec]

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

 

[Jon Peltier]

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

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

- Jon

 

[Qaspec]

I get a run time error 91 at the following line

With ActiveChart.SeriesCollection(1)

Is there a specific item in reference library I need to add?

 

[Jon Peltier]

Error numbers are not nearly as descriptive as error messages.

Error 91 has the message "Object variable not set". The object it is looking
for is ActiveChart.

Select a chart and try again.

However, I suggest you use the non-VBA approach. It is easier to set up and
easier to debug.

- Jon

 

[Qaspec]

I assigned the macro to the chart and click the chart in order to run the
macro. How do I activate the chart in that case?

 

[Jon Peltier]

ActiveSheet.ChartObjects(Application.Caller).Activate

Application.Caller is the shape/chartobject that contains the chart.

- Jon