Argthtjtr

I need your help.
context: I have a VBA to change colors (Green, Orange, Red) to several shapes linked to a % value. The values will change based on a list box. However if the % change, the formatting do not follow.

The macro is as the following - would you know how to "force" the refresh so that the Shape formatting follow any cell changes? below attempt to force it failed.

Private Sub Worksheet_Change(ByVal Target As Range)



    EnableFormatConditionsCalculation = True    

    If Target.Address = "$K$16" Then 

    With Sheets("Resources").Shapes("Oval 1")    

    If Sheets("Resources").Cells(16, 11).Value < 0.95 Then 

    .Fill.ForeColor.RGB = RGB(255, 0, 0)    

    Else If 

    Sheets("Resources").Cells(16, 11).Value > 0.99 Then 

    .Fill.ForeColor.RGB = RGB(0, 255, 0)    

    Else 

    .Fill.ForeColor.RGB = RGB(255, 153, 0)    

    End If    

    End If    

    End With    

    ElseIf Target.Address = "$L$16" Then 

    With Sheets("Resources").Shapes("Oval 2")    

    If Sheets("Resources").Cells(16, 12).Value < 0.95    Then 

    .Fill.ForeColor.RGB = RGB(255, 0, 0)    

    Else If Sheets("Resources").Cells(16, 12).Value > 0.99    Then 

    .Fill.ForeColor.RGB = RGB(0, 255, 0)    

    Else 

    .Fill.ForeColor.RGB = RGB(255, 153, 0)     

    End If    

    End If    

    End With 

    Range("K16:W16").Calculate    

    End If    



End Sub

Try this:

Private Sub Worksheet_Calculate()



    CheckFormat Me.Range("K16"), "Oval 1"

    CheckFormat Me.Range("L16"), "Oval 2"



End Sub



Sub CheckFormat(valueRange As Range, shapeName As String)

    Dim v, clr As Long

    v = valueRange.Value 



    If Len(v) = 0 Then

        clr = RGB(255, 255, 255) '<< EDIT: use white if no value

    Else

        If v < 0.95 Then

            clr = RGB(255, 0, 0)

        ElseIf v > 0.99 Then

            clr = RGB(0, 255, 0)

        Else

            clr = RGB(255, 153, 0)

        End If

    End If

    Me.Shapes(shapeName).Fill.ForeColor.RGB = clr

End Sub

Worksheet_Change will be launched if any of cells will be changed manually by user. It will not be triggered if any of your formulas like RANDBETWEEN will bring new value.

You might be interested in reforging your routine to a VBA formula of a VOLATILE type. Click the link to follow some of the instructions. Long story short - you can place a special command into your User Defined Function so it will run each time something changes in your workbook. And that formula you could place into $K$16 cell.

Hope that helps.