CODEWITHSUNDEEP
vbaexcel
Smallville
Smallville

Reputation: 1

Conditional formatting excel textbox

What is the best way to change the font color of a single textbox based on value of linked cell?

Textbox is located on sheet1 when recording macro it recognizes textbox as ActiveSheet.Shapes.Range(Array("TextBox 1")).Select

I have inserted an image on sheet1, then I inserted textbox's from the insert toolbar. All the textbox's are linked to data on the "stylist" sheet. This sheet gets updated with a macro when the workbook is opened. I'm trying to get the textbox fonts to be red or green based on comparing the value of the linked cell to another cell on the stylist sheet.

enter image description here

Upvotes: 0

Views: 3403

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

With the code you provided and described as having a linked cell, one would assume you are referring to a TextBox from the ActiveX toolbar.

The code for that textbox is located in the Worksheet module. Right click on the sheet tab and select View code to open that module.

enter image description here

If your Linked cell is A1 then we could use a Worksheet_Change event to trigger the code when you change A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$A$1" Then Exit Sub

    Dim x
    x = IIf(Target <= 0, vbRed, vbGreen)

    Me.TextBox1.ForeColor = x

End Sub

You can also use the TextBox1_Change event, this would trigger when the textbox changed.

Private Sub TextBox1_Change()
    Dim x

    x = IIf(TextBox1.Value <= 0, vbRed, vbGreen)
    Me.TextBox1.ForeColor = x

End Sub

You did not indicate what the conditions were so I made up my own.This example uses,

if <=0 then red, else green

The results would be

enter image description here

Upvotes: 0

Linga
Linga

Reputation: 955

Please try this..

' replace Text with your text box name
ActiveSheet.Text.Object.ForeColor = RGB(0, 255, 0)

Upvotes: 0

Related Questions