How to change textbox color based on value in Excel?
In Excel, we can apply the Conditional Formatting function to change the background color based on cell value, but, in this article, I will talk about how to change textbox color based on cell value or value in textbox.
Let’s say, if cell value in A1 is greater than cell value in B1, I want the textbox to be filled with red color, on the contrary, the textbox should be filled with yellow color. Please achieve this job with following steps:
1. Insert a textbox by clicking Developer > Insert > Text Box (ActiveX Control), and then draw a textbox, see screenshot:
2. Then right click the textbox, and select View Code from the context menu to open the Microsoft Visual Basic for Applications window, and then replace the original code with the following VBA code into the blank module:
VBA code: Change textbox color based on cell values:
Private Sub TextBox1_Change() If ActiveSheet.Range("A1").Value > ActiveSheet.Range("B1").Value Then Me.TextBox1.BackColor = vbRed Else Me.TextBox1.BackColor = vbYellow End If End Sub
Note: In the above code, TextBox1 is the textbox name that you inserted, A1 and B1 are the two cells you want to change textbox color based on, please change them to your need.
3. Then save and close the code window, and exit the Design mode, now, if cell A1 is greater than B1, when you type text in the textbox, it will be filled with red color, and if A1 is less than B1, when entering value in the textbox, it will be filled with yellow color, see screenshot:
If you want to change the textbox color based on the value in textbox, for example, when value in textbox is between 1 and 10, the textbox’s filled color is red, the textbox color is green if value is between 11 and 20, if is other values, the textbox color is yellow. To deal with this task, please apply the below VBA code.
1. After inserting a textbox, right click it, and select View Code from the context menu to open the Microsoft Visual Basic for Applications window, and then replace the original code with the following VBA code into the blank module:
VBA code: Change textbox color based on value in textbox:
Private Sub TextBox1_Change() On Error Resume Next 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
Note: In the above code, Textbox1 is the name of the textbox you are inserted, and you can change the values and background color within the code to your own.
2. Then save and close the code window, and exit the Design Mode, now, if you enter the value between 1 and 10 into the textbox, its background color will become red, the value between 11 and 20, the textbox background color will become green, other values, it will become yellow as following screenshot shown:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!