I have about 15 Excel sheets that house 50+ text boxes each over images. Some of these text boxes contain titles (static), some contain dollar values (change with the data), and some contain percentages (change with the data). What I want to do is write a code that selects all of the text boxes, finds only the ones with percents in them, and changes them to green for positive and red for negative. I'm ok with running the macro each time I change the data, but is it possible to write the code so it automatically starts when the data is changed?
Below is what I have so far, and it works, BUT it has some bugs. First, it is changing ALL of the text boxes. The static titles are now green. Second, it is having trouble with dollar values. It is changing all positive dollar values to red. It is correctly changing negative percents to red and positive percents to green. I am guessing I need IF statements in here, but this is my first time trying to create any type of code and I am lost.
Thank you for the help!
Option Explicit Sub conditionalFormatChange() Dim TBox As TextBox Dim Txt As String For Each TBox In ActiveSheet.TextBoxes Txt = TBox.ShapeRange.TextFrame2.TextRange.Characters.Text With TBox.ShapeRange.TextFrame2.TextRange.Font.Fill Select Case Txt Case is > 0 .ForeColor.RGB = RGB(0, 255, 0) Case is < 0 .ForeColor.RGB = RGB(255, 0, 0) End Select .Solid .Transparency = 0 .Visible = msoTrue End With Next TBox End Sub