Conditionally coloring textbox - Excel VBA

[CakzPrimz]

Dear MrExcel,

I need to coloring textbox based on its value. I've tried to write the code below, but it wont works.
If value = 0% then backcolor is Red
If value > 0 and <= 50% then backcolor is Yellow
Else backcolor = Green

Can somebody help me?

Private Sub Train3_Progress_Change()
    Train3_Progress.Value = Format(Train3_Progress.Value, "#0%")
    On Error Resume Next
    Select Case True
    Case Train3_Progress.Value = 0
        Train3_Progress.BackColor = vbRed
    Case Train3_Progress.Value >= 0.1 And Train3_Progress.Value <= 0.5
        Train3_Progress.BackColor = vbYellow
    Case Train3_Progress.Value > 0.5
        Train3_Progress.BackColor = vbGreen
    End Select
End Sub

Thank you
prima

 

[Peter_SSs]

One of the issues with your code is that you are converting the text box's value to a String with the Format() function, so it is no longer a numerical value.
See if this does what you want.

Dim bDisableEvent As Boolean

Private Sub Train3_Progress_Change()
  Dim tbVal As Variant

  If Not bDisableEvent Then
    tbVal = Train3_Progress.Value
    If IsNumeric(tbVal) Then
      On Error Resume Next
      Select Case tbVal
        Case 0
          Train3_Progress.BackColor = vbRed
        Case 0.1 To 0.5
          Train3_Progress.BackColor = vbYellow
        Case Is > 0.5
          Train3_Progress.BackColor = vbGreen
        Case Else
          Train3_Progress.BackColor = xlNone
      End Select
      bDisableEvent = True
      Train3_Progress.Value = Format(tbVal, "#0%")
      bDisableEvent = False
    Else
      Train3_Progress.BackColor = vbWhite
    End If
  End If
End Sub

 

[CakzPrimz]

Dear Peter_SSs,

Glad to inform you that your code works like a charm.
It's a miracle.

Thanks so much, I really appreciate it.
prima

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.