Conditional Formatting on a user form

kevinc1973

New Member
Joined
Jun 20, 2019
Messages
21
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

severynm

Active Member
Joined
Jan 8, 2021
Messages
335
Office Version
  1. 365
Platform
  1. Windows
Hi!

A quick google search returns some good results you can use, such as the below code found from here:
VBA Code:
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.
 
Solution

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,650
Office Version
  1. 2019
Platform
  1. Windows
Hi,
place this code in your userforms code page & see if does what you want

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

New Member
Joined
Jun 20, 2019
Messages
21
Hi!

A quick google search returns some good results you can use, such as the below code found from here:
VBA Code:
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

New Member
Joined
Jun 20, 2019
Messages
21
Hi,
place this code in your userforms code page & see if does what you want

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

Forum statistics

Threads
1,176,393
Messages
5,902,802
Members
434,997
Latest member
bigolbearking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top