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