change text box font color based on value of a cell

[frankdethier]

hi

I'm a VBA newbie, but looking for a solution to change the color of a text box based on the value in another cell. In a nutshell
score < 1.5 --> color = deep red
1.5 < score < 2.5 --> color = light red
2.5 < score < 3.5 --> color = orange
3.5 < score < 4.5 --> color = light green
4.5 < score --> color = deep green

 

[Saurabhj]

Hi,

Welcome to MrExcel board. Please check below code:

txtCode and txtBackColor is textfield name.

Private Sub txtCode_Change()
Select Case txtCode.Value
    Case Is < 1.5
        txtBackColor.BackColor = RGB(200, 0, 0)
    Case Is < 2.5
        txtBackColor.BackColor = vbRed
    Case Is < 3.5
        txtBackColor.BackColor = RGB(255, 165, 0)
    Case Is < 4.5
        txtBackColor.BackColor = vbGreen
    Case Is > 4.5
        txtBackColor.BackColor = RGB(0, 100, 0)
    Case Else
        txtBackColor.BackColor = vbWhite
End Select
End Sub

 

[frankdethier]

thanks but I'm not there yet. So nothing is done/executed yet. This runs automatically?

1. Value in text box is linked to a value in another cell ... so the value between 0 and 5 is actually ''=H33''. Does it work then? Is that seen as a value or as an equation?

2. My text box name is CCBox. So I need to replace in your code above both ''txtCode'' and ''txtBackColor'' with ''CCBox''?

Sorry for my naive questions, but it's the first time I'm experimenting with VBA code.