Nested IF in VBA

[Wilma1969]

Hi All,
I hope someone will be able to assist with expanding the following VBA.
I found this on a thread in one of these forum and it works great in changing the colour of my pivot bar chart

However I need to expand this to take in account the following.

I would like to change the colour of the bar on my chart depending on the value, 0-40% - Red 41-85% - Amber 86-100% - Green.
I know its a nested IF I nee but VBA is not my strong point.
Thanks in advance.

Wilma

Sub Color_Chart_Series()

Dim cht As Chart, sc As Series, i As Integer

Set cht = Sheets("Sheet5").ChartObjects(1).Chart

For Each sc In cht.SeriesCollection

For i = 1 To UBound(sc.Values)

If sc.Values(i) < 0.85 Then
sc.Points(i).Interior.ColorIndex = 3
Else
sc.Points(i).Interior.ColorIndex = 17
End If

Next i

Next sc

End Sub

 

[Joe4]

Welcome to the Board!

You can structure it like this:

[COLOR=#333333]If sc.Values(i) >= 0.86 Then[/COLOR]
[COLOR=#333333]    sc.Points(i).Interior.ColorIndex = ...[/COLOR]
[COLOR=#333333]Else
[/COLOR][COLOR=#333333]    If sc.Values(i) >= 0.41 Then[/COLOR]
[COLOR=#333333]        sc.Points(i).Interior.ColorIndex = ...
[/COLOR]    [COLOR=#333333]Else
[/COLOR][COLOR=#333333]        sc.Points(i).Interior.ColorIndex = ...[/COLOR]
[COLOR=#333333]    End If
End If[/COLOR]

 

[Wilma1969]

Hi Joe4
Thanks that great.
Iv probably been focusing on this for too long,,,,,,,,
To ensure that I get the same results each time do I need to use a between 0-41, 42-86, and >86?
I cant get the correct colours to display.

 

[Joe4]

To ensure that I get the same results each time do I need to use a between 0-41, 42-86, and >86?

No. The way a nested IF statement works is that it stops when it finds the FIRST true condition. So, we start at the top end (86%) and work our way down.

I cant get the correct colours to display.

Was it working in your original example?
I was assuming that you have good working code that you wanted to expand on.