Conditionally formatting ActiveX TextBox based on value

Hi again Jo,

Many thanks for your feedback.

Ok on using the Change event rather than the LostFocus event to trigger the colour change. Whenever possible I avoid using the Change event for the simple reason that the event is triggered each time any change is made to any character displayed in the TextBox rather than when all changes have been completed, but in some situations that's exactly what's needed.

As far as your second point is concerned (it's interesting to see that you use the worksheet CodeName - good for you!! ) it seems that Excel VBA finds a statement such as "Case Is < wksThresholds.Range("B1").Value" to be "too complex", as demonstrated by the following code:

Sub Test()

    Dim dValue As Double

    dValue = 100

    Select Case dValue

           Case Is < wksThresholds.Range("B1").Value    '   Has a value of 50
                MsgBox "OK"

    End Select

End Sub

The attached workbook uses the following version of the previous routine, and it seems to work:

Private Sub UpdateBackground(ctl As Object)

    Dim dLevel_1    As Double
    Dim dLevel_2    As Double
    Dim dLevel_3    As Double
    Dim dLevel_4    As Double

    dLevel_1 = wksThresholds.Range("B1").Value
    dLevel_2 = wksThresholds.Range("B2").Value
    dLevel_3 = wksThresholds.Range("B3").Value
    dLevel_4 = wksThresholds.Range("B4").Value

    Select Case ctl.Value

           Case Is < dLevel_1
                ctl.BackColor = &H8080FF

           Case Is < dLevel_2
                ctl.BackColor = &H80C0FF

           Case Is < dLevel_3
                ctl.BackColor = &H80FF80

           Case Is < dLevel_4
                ctl.BackColor = &HFF8080

    End Select

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M