Conditional Formatting on a user form

[kevinc1973]

Hello all,
I have a workbook in Excel that i have user forms that write to data sheets, I have Conditional Formatting on the Data Sheets, I was wondering if there is a way to get conditional formatting in the text boxes on a user form as well? I would like a range for a textbox8 turn blue when it falls between 1 -89 and turns red when greater than 110 stays white when its between 90 and 110. Any help with this would be greatly appreciated. Thanks in advance.
Kevin

 

[severynm]

Hi!

A quick google search returns some good results you can use, such as the below code found from here:

Private Sub TextBox1_Change()
    Select Case TextBox1.Value
        Case 1 To 10:
            TextBox1.BackColor = vbRed
        Case 11 To 20:
            TextBox1.BackColor = vbGreen
        Case Else:
            TextBox1.BackColor = vbYellow
    End Select
End Sub

The event TextBox_Change triggers any time the text box is changed, so you will need one of these for each text box you want to format. The code above uses a case statement for the logic, but you could use an if statement instead. Lastly, in case you do not want to use the built in colors, you can make use of the RGB function for custom colors. Either way, that should be enough to get you going.

 

[dmt32]

Hi,
place this code in your userforms code page & see if does what you want

Private Sub TextBox8_Change()
    Dim m As Variant
    With Me.TextBox8
    m = Application.Match(Val(.Value), Array(0, 1, 90, 111), 1)
    .BackColor = Choose(m, vbWhite, vbBlue, vbWhite, vbRed)
    End With
End Sub

Dave

 

[kevinc1973]

Hi!

A quick google search returns some good results you can use, such as the below code found from here:

Private Sub TextBox1_Change()
    Select Case TextBox1.Value
        Case 1 To 10:
            TextBox1.BackColor = vbRed
        Case 11 To 20:
            TextBox1.BackColor = vbGreen
        Case Else:
            TextBox1.BackColor = vbYellow
    End Select
End Sub

The event TextBox_Change triggers any time the text box is changed, so you will need one of these for each text box you want to format. The code above uses a case statement for the logic, but you could use an if statement instead. Lastly, in case you do not want to use the built in colors, you can make use of the RGB function for custom colors. Either way, that should be enough to get you going.

Thank you that did it. Thanks for the help.

 

[kevinc1973]

Hi,
place this code in your userforms code page & see if does what you want

Private Sub TextBox8_Change()
    Dim m As Variant
    With Me.TextBox8
    m = Application.Match(Val(.Value), Array(0, 1, 90, 111), 1)
    .BackColor = Choose(m, vbWhite, vbBlue, vbWhite, vbRed)
    End With
End Sub

Dave

Thanks I also tried yours and that worked as well. Thanks for the help.