Conditionally formatting a percentage

[KenC53]

I have a TextBox that references a cell on another sheet. I am formatting the textbox as a percentage and want to apply a conditional format to the textbox. The code returns an error when it gets to the section that conditionally formats the color.

Private Sub TextBox2_Change()

Dim sTemp As String, val As Single
sTemp = TextBox2.Text
If Not (InStr(1, sTemp, "%", vbTextCompare) < 1 _
And IsNumeric(sTemp)) Then _
Exit Sub '<<<<<
val = CSng(sTemp)
TextBox2.Text = Format(IIf(val < 1, val, val / 100), "0%")

If TextBox2.Text <= 0.12 Then TextBox2.BackColor = RGB(117, 146, 60)
If TextBox2.Text <= 0.12 Then TextBox2.ForeColor = RGB(0, 0, 0)
If TextBox2.Text > 0.12 Then TextBox2.BackColor = RGB(222, 42, 0)
If TextBox2.Text > 0.12 Then TextBox2.ForeColor = RGB(0, 0, 0)
If TextBox2.Text = "-" Or TextBox2.Text = "" Then TextBox2.BackColor = RGB(128, 128, 128)
If TextBox2.Text = "-" Or TextBox2.Text = "" Then TextBox2.ForeColor = RGB(0, 0, 0)
End Sub


The error occurs at the first If TextBox2.Text <= 0.12 statement. If i disable the section that applies the %, the conditional formatting works correctly but then the number is not a percentage.

Any suggestions

 

[Seti]

The problem is that you are trying to test a text string against a numeric value. The easiest solution would be to create another variable, or redefine val before formatting it as text, then use that variable in your conditional formatting section.

 

[KenC53]

Forgive me I'm a newbie. How would I create the new variable then use that variable in my conditional formatting section?

 

[Seti]

Try something like this: (add val2 to your Dim line as Single)

Change

val = CSng(sTemp)
TextBox2.Text = Format(IIf(val < 1, val, val / 100), "0%")

to

val = CSng(sTemp)
val2=IIf(val < 1, val, val / 100)
TextBox2.Text = Format(val2, "0%")


and

If TextBox2.Text <= 0.12 Then TextBox2.BackColor = RGB(117, 146, 60)
If TextBox2.Text <= 0.12 Then TextBox2.ForeColor = RGB(0, 0, 0)
If TextBox2.Text > 0.12 Then TextBox2.BackColor = RGB(222, 42, 0)
If TextBox2.Text > 0.12 Then TextBox2.ForeColor = RGB(0, 0, 0)

to

If val2 <= 0.12 Then TextBox2.BackColor = RGB(117, 146, 60)
If val2 <= 0.12 Then TextBox2.ForeColor = RGB(0, 0, 0)
If val2 > 0.12 Then TextBox2.BackColor = RGB(222, 42, 0)
If val2 > 0.12 Then TextBox2.ForeColor = RGB(0, 0, 0)

 

[KenC53]

Works perfect! Thank you Seti!

 

[Seti]

Happy to oblige.