Data bar formatting to change colour dependant on value

[Pauljj]

Morning all,

In cell E5 I have a manually input value of 58.8%

In cell F5 I currently have a calculated value of 52.3%

I want E5 to have a bar that fills the cell space dependant on the value in it, so in this example the fill would be 58.8% of the cell....and if the value is greater then F5 the fill is green but if its less then or equal to, it's red

I've had a play around but not been able to find a solution, any ideas ?

 

[Micron]

VBA - get width of cell. Set shape width (length?) to 58.8% of that width. If the cell value is greater than F5, set the shape interior colour to green, otherwise, red.
One trick would be how to deal with anyone changing the column width.
That is all speculation on my part as I have never coded for that. There might be some conversions that need to be done; i.e. column width and shape length might be different units of measure.

 

[Micron]

Something for you to play with.
The index of my shape on sheet 003 is 7 (you can swap in the shape name instead).
D16 is the cell containing my percentage as a number (e.g. 50, not 50%). E16 is the cell to compare it to.
You can play with the colour mixing (RGB values)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colWidth As Double

If Not Target.Address = "$D$16" Then Exit Sub
colWidth = Columns(3).Width

With Sheets("003")
    .Shapes(7).Width = colWidth * CDbl(Target) / 100
    If Target < Range("E16") Then
        .Shapes(7).Fill.ForeColor.RGB = RGB(255, 0, 0)
    Else
        .Shapes(7).Fill.ForeColor.RGB = RGB(0, 255, 0)
    End If
End With

End Sub

Not sure CDbl is needed but I have to drop this for now so can't play around with it at the moment, but it seems to work within the parameters I gave you.