Results 1 to 4 of 4

Thread: Conditionally coloring textbox - Excel VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditionally coloring textbox - Excel VBA

    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?

    Code:
    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

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,964
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Conditionally coloring textbox - Excel VBA

    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.
    Code:
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Oct 2017
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditionally coloring textbox - Excel VBA

    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

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,964
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Conditionally coloring textbox - Excel VBA

    You are very welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •