How to change font color in a TextBox wherein the value of the TextBox is on another Cell.

[ayhann]

Hello -

I'm totally newbie on Excel VB. i just happened that i have the tool that we use and just editing the code on it.
Now, I would like to ask on how can i change the font of my txtBox, if the value of my textBox is 90% or above it will turn it to green, while if 89% below the font would be red.


This would be its look like on a form

Productivity
95%

Utilization
100%

Whenever i click the word productivity it will show the value of a cell on my sheet (Sempra.txtProd = Format(Sheets("Data").Range("g3"), "0.00%")) , something like it would refresh the tool.
but this cell has also a formula on it (=IFERROR(F3/E3,"")).

i tried to use this code.

If the txtProd.value >= 90 Then
txtprod.forecolor = vbGreen
EndIf

am i doing it wrong? Please help.

Thank you!

 

[Kamolga]

If you put a textbox in a form (in my case it is called textbox1), you want it to type red if you type anythink below 90 else green then 2x click it in VBA and you can use (important is the event, textbox1_change is good)

Private Sub TextBox1[B]_Change[/B]()
If TextBox1.Value >= 90 Then
    TextBox1.ForeColor = vbGreen
    Else
    TextBox1.ForeColor = vbRed
End If
End Sub

 

[ayhann]

If you put a textbox in a form (in my case it is called textbox1), you want it to type red if you type anythink below 90 else green then 2x click it in VBA and you can use (important is the event, textbox1_change is good)

Private Sub TextBox1[B]_Change[/B]()
If TextBox1.Value >= 90 Then
    TextBox1.ForeColor = vbGreen
    Else
    TextBox1.ForeColor = vbRed
End If
End Sub

Thanks @Kamolga on replying.

I have put these codes in my form


----------
Private Sub txtProd_Change()


If txtProd.Value >= 90 Then
txtProd.ForeColor = vbGreen
Else
txtProd.ForeColor = vbRed

End If
End Sub
--------------


- however when i click it on VBA it did not change to red or green.
Maybe because the value of my textbox has a percentage or the value of my textbox was on the sheet?



I have this formula on my form
Private Sub bRefresh_Click()
(Name of Sheet2).txtProd = Format(Sheets("Data").Range("g3"), "0.00%")


Sheet2
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total Actual Processing Time[/TD]
[TD]Sum of AHT[/TD]
[TD]Productivity[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]53.25[/TD]
[TD]30[/TD]
[TD]56.34%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 525"]
<tbody>[TR]
[TD="colspan: 3"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

G3 - = IFERROR(F3/E3,"")

Again thanks for the help!

 

[Kamolga]

If your textbox is on a form, what is

(Name of Sheet2).txtProd ?

If you want the cells of the sheet to change color, simply use conditional formatting.

The change event will adapt color while you type in the cell. maybe you can try

Private Sub [LEFT][COLOR=#333333][FONT=Verdana]txtProd[/FONT][/COLOR][/LEFT]_AfterUpdate()<strike></strike>

Otherwise instead of _change if you are not typing, you can integrate it to your brefresh_click or after you have userform1.show or I would put it on userform1_Activate event since it is part of a form (

Of course, if your are dealing with %, you should use 0.9 instead of 90 to become green)​

​