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

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

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" wrote:

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

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Qaspec" wrote in message
...
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" wrote:

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

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Qaspec" wrote in message
...
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" wrote:

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

ActiveSheet.ChartObjects(Application.Caller).Activ ate

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

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


"Qaspec" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Qaspec" wrote in message
...
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" wrote:

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