Conditional Format Bar Chart

  • Thread starter Thread starter Qaspec
  • Start date Start date
Q

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
 
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?
 
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
 
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?
 
ActiveSheet.ChartObjects(Application.Caller).Activate

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

- Jon
 
Back
Top