Text Box dynamic Color Fill

[Guest]

Hi,

I have a textbox in excel that is linked to a cell. Depending if the cell
value is positive or negative, I would like to change the textbox background
color (fill). Any advice? Tks a lot! - Castor

 

[Stefan Hägglund [MSFT]]

Hi Castor!

You can use a macro, like the sample below to change the background color in
the textbox.

You have to update the name of the textbox in the macro, if it's not "Text
Box 1", and the cell where you want to check for a positive/negative values.

Sub ChangeTextboxFillColor()

TheActiveCell = ActiveCell.Address
If ActiveSheet.Range("A1") >= 0 Then
ActiveSheet.Shapes("Text Box 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11

Else

ActiveSheet.Shapes("Text Box 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 18
End If
Range(TheActiveCell).Select
End Sub



Best regards

Stefan Hägglund
Microsoft

 

[Dave Peterson]

I used a textbox from the Drawing toolbar and I assumed that A1 would change by
typing (not a formula change).

I could use a worksheet event. Right click on the worksheet tab that holds the
textbox. Select view code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim TBox As TextBox

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Set TBox = Me.TextBoxes("text box 1")

With Target
If IsNumeric(.Value) Then
If .Value >= 0 Then
TBox.Interior.ColorIndex = 3
Else
TBox.Interior.ColorIndex = 6
End If
Else
TBox.Interior.ColorIndex = xlNone
End If
End With

End Sub