Automatically change font color in text box depending on value

[onthegreen03]

Hi. I have a small and probably simple problem. I am using text boxes linked to cells with numbers in them. The numbers change frequently and I want to have the font color change as the numbers change. Red for negative number, green for positive and black if zero. Can someone help me with this? I did some reading and found a similar question from a few years ago posted here but the solution didn't work for me. Thank you in advance.

 

[GWteB]

You could use conditional formatting.

 

[Rick Rothstein]

Are you TextBoxes located on a UserForm or directly on a worksheet? If on a worksheet, is it an ActiveX TextBox?

 

[onthegreen03]

Hi Rick - Thank you for taking a look at this. I inserted the normal text boxes directly into the worksheet and then linked it to particular cells in the same worksheet. I did not use the ActiveX text box but am open to ideas if it helps solve the problem in an easier manner.

 

[GWteB]

With ActiveX text boxes you could use VBA. If you are not familiar with VBA: Ribbon > Developer tab > press Design button, and right click on the first text box. On the context pop-up choose Display Program Code. The VBA editor opens and in the pane at the right hand side the name of an "empty" subroutine appears. Paste the following code in the Change event subroutine of each text box as desired.
The ColorChange sub is called by the Change event routine of the/each text box. Paste at the top of the code module the four lines with the Option statement and the declarations of the three different colors.

Option Explicit
Private Const cBlack    As Long = &H80000008
Private Const cRed      As Long = &HFF&
Private Const cGreen    As Long = &H8000&


Private Sub TextBox8_Change()
    '             /|\
    On Error Resume Next        '      \|/
    Call ColorChange(Me.Range(Me.TextBox8.LinkedCell))
    On Error GoTo 0
End Sub


Private Sub ColorChange(ByRef argCell As Range)
    With argCell
        If IsNumeric(.Value) Then
            If .Value > 0 Then
                .Font.Color = cGreen
            ElseIf .Value < 0 Then
                .Font.Color = cRed
            Else
                .Font.Color = cBlack
            End If
        Else
            .Font.Color = cBlack
        End If
    End With
End Sub

 

[onthegreen03]

Great, thank you! I appreciate the information.

 

[GWteB]

You are welcome and thanks for letting me know.