Creating full-spectrum color gradients in bar chart based on input value

[Ushiwakka]

I'm creating a resource for the stats behind the mechanics of a video game I'm designing and would like the value of each stat to scale from red (lowest) through yellow, to green, and finally cyan (highest). I'd post an image link but I'm not allowed to yet; the stat bars for the Pokemon on the Smogon site is very similar to what I'm going for.

Ideally the color and length of the bar would automate once a number is input. In Excel the fill effects only allow for two colors rather than a gradual spectrum; any help on how to implement this is much appreciated. (using version 2016)

 

[Worf]

Welcome

Something like this? Do you have the image icon shown below?

 

[Ushiwakka]

Welcome

Something like this? Do you have the image icon shown below?

View attachment 28967

View attachment 28968

Yeah I believe I've gotten to that section previously, but is there a way to get solid color bars that scale the color based on the input value like this?:

https://imgur.com/l4yFW4o

 

[Worf]

Try this code:

Sub Grad()
Dim p As Point, sc As Series, arr, i%, fr!
Set sc = ActiveChart.FullSeriesCollection(1)
arr = sc.Values
For i = LBound(arr) To UBound(arr)
    Set p = ActiveChart.FullSeriesCollection(1).Points(i)
    p.Format.Fill.TwoColorGradient msoGradientVertical, 1
    fr = arr(i) / WorksheetFunction.Max(arr)
    Select Case True
        Case fr < 0.33
            p.Format.Fill.ForeColor.RGB = RGB(250, 10, 10)
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.99
        Case fr > 0.33 And fr < 0.66
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.33 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 10), 0.99
        Case fr > 0.66 And fr < 0.9
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.33 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 10), 0.66 / fr
        Case fr > 0.9
            p.Format.Fill.ForeColor.RGB = RGB(10, 250, 250)
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0 / fr
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.33 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 10), 0.66 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 250), 0.9 / fr
    End Select
Next
End Sub

 

[Ushiwakka]

Try this code:

View attachment 29078

Sub Grad()
Dim p As Point, sc As Series, arr, i%, fr!
Set sc = ActiveChart.FullSeriesCollection(1)
arr = sc.Values
For i = LBound(arr) To UBound(arr)
    Set p = ActiveChart.FullSeriesCollection(1).Points(i)
    p.Format.Fill.TwoColorGradient msoGradientVertical, 1
    fr = arr(i) / WorksheetFunction.Max(arr)
    Select Case True
        Case fr < 0.33
            p.Format.Fill.ForeColor.RGB = RGB(250, 10, 10)
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.99
        Case fr > 0.33 And fr < 0.66
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.33 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 10), 0.99
        Case fr > 0.66 And fr < 0.9
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.33 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 10), 0.66 / fr
        Case fr > 0.9
            p.Format.Fill.ForeColor.RGB = RGB(10, 250, 250)
            p.Format.Fill.GradientStops.Insert RGB(250, 10, 10), 0 / fr
            p.Format.Fill.GradientStops.Insert RGB(250, 250, 10), 0.33 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 10), 0.66 / fr
            p.Format.Fill.GradientStops.Insert RGB(10, 250, 250), 0.9 / fr
    End Select
Next
End Sub

Thank you, but is it possible to get solid colors as in the image I posted? Each bar wouldn't have it's own gradient, but the range would be on a gradient that narrows to a single color for a given value. Or is the image you posted not indicative of the final product?

 

[Worf]

New version:

Sub Grad()
Dim p As Point, sc As Series, arr, i%, fr!
arr = ActiveChart.FullSeriesCollection(1).Values
For i = LBound(arr) To UBound(arr)
    Set p = ActiveChart.FullSeriesCollection(1).Points(i)
    fr = arr(i) / WorksheetFunction.Max(arr)
    Select Case True
        Case fr < 0.33
            p.Format.Fill.ForeColor.RGB = RGB(250, 10, 10)
        Case fr > 0.33 And fr < 0.66
            p.Format.Fill.ForeColor.RGB = RGB(250, 250, 10)
        Case fr > 0.66 And fr < 0.9
            p.Format.Fill.ForeColor.RGB = RGB(10, 250, 10)
        Case fr > 0.9
            p.Format.Fill.ForeColor.RGB = RGB(10, 250, 250)
    End Select
Next
End Sub