Conditional Formatting on Userform

[kidwispa]

Hi all,

I was wondering if someone could give me some assistance on the following issue. I have created the below userform, and when an Agent name is selected from the Combo Box and the "Retrieve" command button is pressed, the text boxes below populate as shown (NB:agent name has been removed)

What I am trying to do is show conditional formatting in each of the text boxes, all with the same criteria:

"YES" = Green background
"NO" = Red background
"-" = Grey background

Here is the code I did in order to use the combobox/command box to populate the text boxes...

Private Sub CommandButton5_Click()
Dim criteria As String
Dim rng As Range
Dim result

criteria = ComboBox1.Text
If Trim(criteria) <> "" Then
result = Application.Match(criteria, Worksheets("QtrData").Range("B:B"), 0)
If Not IsError(result) Then
With Worksheets("QtrData")
TextBox5.Text = .Range("I:I")(result).Text
TextBox6.Text = .Range("M:M")(result).Text
TextBox10.Text = .Range("R:R")(result).Text
TextBox14.Text = .Range("W:W")(result).Text
TextBox18.Text = .Range("AB:AB")(result).Text
TextBox22.Text = .Range("AC:AC")(result).Text
TextBox2.Text = .Range("AH:AH")(result).Text
TextBox7.Text = .Range("AL:AL")(result).Text
TextBox11.Text = .Range("AQ:AQ")(result).Text
TextBox15.Text = .Range("AV:AV")(result).Text
TextBox19.Text = .Range("BA:BA")(result).Text
TextBox23.Text = .Range("BB:BB")(result).Text
TextBox3.Text = .Range("BG:BG")(result).Text
TextBox8.Text = .Range("BK:BK")(result).Text
TextBox12.Text = .Range("BP:BP")(result).Text
TextBox16.Text = .Range("BU:BU")(result).Text
TextBox20.Text = .Range("BZ:BZ")(result).Text
TextBox24.Text = .Range("CA:CA")(result).Text
TextBox4.Text = .Range("CF:CF")(result).Text
TextBox9.Text = .Range("CJ:CJ")(result).Text
TextBox13.Text = .Range("CO:CO")(result).Text
TextBox17.Text = .Range("CT:CT")(result).Text
TextBox21.Text = .Range("CY:CY")(result).Text
TextBox25.Text = .Range("CZ:CZ")(result).Text
TextBox26.Text = .Range("DA:DA")(result).Text

End With
Else
MsgBox "Unable to find reference"
End If
Else
MsgBox "Unable to find reference"
End If

End Sub


Hope you can help!!!

 

[Akashwani]

Hi,

I don't write or understand vba so i am unable to help you further with code, sorry. I have copied this from a User Form that I have...

If tbData2.Text = "YES" Then tbData2.BackColor = RGB(255, 255, 0)
If tbData2.Text = "YES" Then tbData2.ForeColor = RGB(0, 0, 0)
If tbData2.Text = "NO" Then tbData2.BackColor = RGB(0, 0, 0)
If tbData2.Text = "NO" Then tbData2.ForeColor = RGB(255, 255, 255)

tbData2 is the name of a textbox

Here is a link to all the colours available in Excel

http://www.mvps.org/dmcritchie/excel/colors.htm

For your situation I would guess that you need something like this....

If TextBox5.Text = "YES" Then TextBox5.BackColor = RGB(0, 255, 0)
If TextBox5.Text = "YES" Then TextBox5.ForeColor = RGB(0, 0, 0)
If TextBox5.Text = "NO" Then TextBox5.BackColor = RGB(255, 0, 0)
If TextBox5.Text = "NO" Then TextBox5.ForeColor = RGB(0, 0, 0)
If TextBox5.Text = "-" Then TextBox5.BackColor = RGB(128, 128, 128)
If TextBox5.Text = "-" Then TextBox5.ForeColor = RGB(0, 0, 0)

I guess Ideally though the code needs to loop/check every TextBox rather than you having to apply the above to each individual TextBox.

Sorry that I couldn't have been of more help.

Good luck.

 

[kidwispa]

Thanks for your help Akashwani. I'm not sure entirely how to loop so have just pasted in for each textbox... works fine!

 

[kidwispa]

Can anyone explain why this works:

If TextBox51.Value <= "1" Then TextBox51.BackColor = RGB(0, 255, 0)
If TextBox51.Value <= "1" Then TextBox51.ForeColor = RGB(0, 0, 0)
If TextBox51.Value > "1" Then TextBox51.BackColor = RGB(255, 0, 0)
If TextBox51.Value > "1" Then TextBox51.ForeColor = RGB(0, 0, 0)
If TextBox51.Text = "-" Then TextBox51.BackColor = RGB(128, 128, 128)
If TextBox51.Text = "-" Then TextBox51.ForeColor = RGB(0, 0, 0)


So when TextBox51 is;

Less than or equal to 1 = Green
Greater than 1 = Red
Shows a "-" = Grey

But this doesn't:

If TextBox43.Value <= "10%" Then TextBox43.BackColor = RGB(0, 255, 0)
If TextBox43.Value <= "10%" Then TextBox43.ForeColor = RGB(0, 0, 0)
If TextBox43.Value > "10%" Then TextBox43.BackColor = RGB(255, 0, 0)
If TextBox43.Value > "10%" Then TextBox43.ForeColor = RGB(0, 0, 0)
If TextBox43.Text = "-" Then TextBox43.BackColor = RGB(128, 128, 128)
If TextBox43.Text = "-" Then TextBox43.ForeColor = RGB(0, 0, 0)


For some reason all answers turn the box red. I have tried removing the % sign but it still doesnt work how I'd like it to...

What I am trying to achieve for TextBox43 is:

Less than or equal to 10% = Green
Greater than 10% = Red
Shows a "-" = Grey

Thanks again!

 

[Ruddles]

Because "10%" is a string of three characters rather than a value.

Replace it with 0.10 (no quotes).

 

[kidwispa]

Thanks for the suggestion, although it's still not working correctly...


This is the full code I've done (with notes in red)

Private Sub CommandButton7_Click()
Dim criteria As String
Dim rng As Range
Dim result

criteria = ComboBox2.Text
If Trim(criteria) <> "" Then
result = Application.Match(criteria, Worksheets("QtrData").Range("B:B"), 0)
If Not IsError(result) Then
With Worksheets("QtrData")

TextBox51.Text = .Range("G:G")(result).Text
If TextBox51.Value <= 0.01 Then TextBox51.BackColor = RGB(0, 255, 0)
If TextBox51.Value <= 0.01 Then TextBox51.ForeColor = RGB(0, 0, 0)
If TextBox51.Value > 0.01 Then TextBox51.BackColor = RGB(255, 0, 0)
If TextBox51.Value > 0.01 Then TextBox51.ForeColor = RGB(0, 0, 0)
If TextBox51.Text = "-" Then TextBox51.BackColor = RGB(128, 128, 128)
If TextBox51.Text = "-" Then TextBox51.ForeColor = RGB(0, 0, 0)
(The above should return green when less than or equal to 1%, red if above 1% and grey if "-")
TextBox47.Text = .Range("K:K")(result).Text
If TextBox47.Value <= 3 Then TextBox47.BackColor = RGB(0, 255, 0)
If TextBox47.Value <= 3 Then TextBox47.ForeColor = RGB(0, 0, 0)
If TextBox47.Value > 3 Then TextBox47.BackColor = RGB(255, 0, 0)
If TextBox47.Value > 3 Then TextBox47.ForeColor = RGB(0, 0, 0)
If TextBox47.Text = "-" Then TextBox47.BackColor = RGB(128, 128, 128)
If TextBox47.Text = "-" Then TextBox47.ForeColor = RGB(0, 0, 0)
(The above should return green when less than or equal to 3, red if above 3 and grey if "-")

TextBox43.Text = .Range("P:P")(result).Text
If TextBox43.Value <= 0.1 Then TextBox43.BackColor = RGB(0, 255, 0)
If TextBox43.Value <= 0.1 Then TextBox43.ForeColor = RGB(0, 0, 0)
If TextBox43.Value > 0.1 Then TextBox43.BackColor = RGB(255, 0, 0)
If TextBox43.Value > 0.1 Then TextBox43.ForeColor = RGB(0, 0, 0)
If TextBox43.Text = "-" Then TextBox43.BackColor = RGB(128, 128, 128)
If TextBox43.Text = "-" Then TextBox43.ForeColor = RGB(0, 0, 0)
(The above should return green when less than or equal to 1%, red if above 10% and grey if "-")

TextBox39.Text = .Range("T:T")(result).Text
If TextBox39.Value = 0 Then TextBox39.BackColor = RGB(0, 255, 0)
If TextBox39.Value = 0 Then TextBox39.ForeColor = RGB(0, 0, 0)
If TextBox39.Value > 0 Then TextBox39.BackColor = RGB(255, 0, 0)
If TextBox39.Value > 0 Then TextBox39.ForeColor = RGB(0, 0, 0)
If TextBox39.Text = "-" Then TextBox39.BackColor = RGB(128, 128, 128)
If TextBox39.Text = "-" Then TextBox39.ForeColor = RGB(0, 0, 0)
(The above should return green when equal to 0, red if above 0 and grey if "-")

TextBox35.Text = .Range("Z:Z")(result).Text
If TextBox35.Value <= 0.01 Then TextBox35.BackColor = RGB(0, 255, 0)
If TextBox35.Value <= 0.01 Then TextBox35.ForeColor = RGB(0, 0, 0)
If TextBox35.Value >= 0.01 Then TextBox35.BackColor = RGB(255, 0, 0)
If TextBox35.Value >= 0.01 Then TextBox35.ForeColor = RGB(0, 0, 0)
If TextBox35.Text = "-" Then TextBox35.BackColor = RGB(128, 128, 128)
If TextBox35.Text = "-" Then TextBox35.ForeColor = RGB(0, 0, 0)
(The above should return green when less than or equal to 1%, red if above 1% and grey if "-")

TextBox31.Text = .Range("AC:AC")(result).Text
(This doesn't require any conditions as will return the result only)

End With
Else
MsgBox "Unable to find reference"
End If
Else
MsgBox "Unable to find reference"
End If

End Sub

I really need to get this to work so any input would be appreciated!

 

[Ruddles]

TextBox43.Text = .Range("P:P")(result).Text
If TextBox43.Value <= 0.1 Then TextBox43.BackColor = RGB(0, 255, 0)
If TextBox43.Value <= 0.1 Then TextBox43.ForeColor = RGB(0, 0, 0)
If TextBox43.Value > 0.1 Then TextBox43.BackColor = RGB(255, 0, 0)
If TextBox43.Value > 0.1 Then TextBox43.ForeColor = RGB(0, 0, 0)
If TextBox43.Text = "-" Then TextBox43.BackColor = RGB(128, 128, 128)
If TextBox43.Text = "-" Then TextBox43.ForeColor = RGB(0, 0, 0)
(The above should return green when less than or equal to 1%, red if above 10% and grey if "-")

I assume that's a typo: it's 10% rather than 1%, yes?

In what way is it not working? Is none of it working? Is some of it working but not all? Which bits are and which bits aren't, and for what values?

 

[uk747]

for the green and red you can use = vbGreen and = vbRed

8 that you can user are vbBlack, vbBlue, vbCyan, vbGreen, vbMagenta, vbRed, vbWhite, vbYellow