Radar Fill colour

[REV AJR]

Please can any one help, I have created a Radar Graph uisng the following criteria
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=255><COLGROUP><COL style="WIDTH: 109pt" width=145><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff; WIDTH: 109pt; HEIGHT: 38.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 height=51 rowSpan=3 width=145>Parameter
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 83pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=110>Rating</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; WIDTH: 83pt; HEIGHT: 25.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=34 rowSpan=2 width=110>Weighted Score</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl78 height=17 width=145> 1. Supplier Score Card</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl81 align=right>45%</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class=xl82 height=17 width=145> 2. Spend</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl81 align=right>15%</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class=xl82 height=17 width=145> 3. Strategic to Busines</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl81 align=right>10%</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class=xl82 height=17 width=145> 4. Relationship</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl81 align=right>10%</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class=xl82 height=17 width=145> 5. Passport to Work</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl81 align=right>10%</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class=xl82 height=17 width=145> 6. SQM Audit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl81 align=right>0%</TD></TR></TBODY></TABLE>

I now want to create VBA code to change the colour of the data series depending on 3 criteria. I have tried every which way I know and have failed totaly.

 

[p45cal]

"depending on 3 criteria"
what are those criteria?

 

[REV AJR]

Sorry I can not count, inflation as increased it to 4 criteria

IFthe overall score <0.7,then the colour needs to be RED

IF the overall score is >=0.7 and <0.8),then the colour needs to be Brown

IF the overall score is >=0.8 and <0.95),then the colour needs to be Silver

IF the overall score is >=0.95 and <=1, then the colour needs to be GOLD

Thanks in advance

 

[p45cal]

Adapt the following to your circumstances. The following adjusts the first data series and assumes the radar chart is a Filled Radar type:

Sub blah()
With ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1)
    mycol = xlNone
    Select Case Application.Sum(.Values)
      Case Is < 0.7: mycol = 3  'red
      Case Is < 0.8: mycol = 53  'brown
      Case Is < 0.95: mycol = 15  'silver
      Case Is <= 1: mycol = 44  'gold
    End Select
    .Interior.ColorIndex = mycol
End With
End Sub

 

[REV AJR]

Thanks for the quick response, It works a treat. I can now move on to the next stage.