Conditional formatting on multiple PERCENTAGE text boxes only

Hello everyone!

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

Re: Conditional formatting on multiple PERCENTAGE text boxes only

I should have been a little more clear in how I "wrote" this code. I Googled my problem in numerous ways and pieced together random codes other people had already made. I have 0 experience in code writing, literally. So I don't even know what the "Value of a String" means.

My text boxes with values in them are linked to other cells that have formulas in them. Some text boxes grab dollar amounts, and some grab percentages. All of these are YTD data, so they adjust once I add in the new month's data. But after the new month's data is in, some of the text boxes are now negative when they were positive last month. Up until this point, I have just manually selected each text box and changed it to red or green, depending on how it changed.

Basically, if you don't think the string section needs to be in there, toss it out and let me know why you did that so I can learn!

Edit: I tried to do the Locals Window and when I am hovering over "Sub ConditionalFormatChange" it says:
Expression-----Value-----Type
TBox-----Nothing-----TextBox
Txt-----""-----String

Does that help?

Re: Conditional formatting on multiple PERCENTAGE text boxes only

I am getting the hang of this. If I keep going through the steps it eventually lands on a text box that has a percent in it. If I expand the TBox expression it lists my Formula under Font, $F$48, and lists the Text as 9.34% under ShapeRange. Then under Txt expression it lists Value as 9.34% and String type.

Re: Conditional formatting on multiple PERCENTAGE text boxes only

I am getting the hang of this. If I keep going through the steps it eventually lands on a text box that has a percent in it. If I expand the TBox expression it lists my Formula under Font, $F$48, and lists the Text as 9.34% under ShapeRange. Then under Txt expression it lists Value as 9.34% and String type.

Re: Conditional formatting on multiple PERCENTAGE text boxes only

Attached a screenshot.
[ATTACH=CONFIG]69358[/ATTACH]

Re: Conditional formatting on multiple PERCENTAGE text boxes only

I created the most basic sample sheet but it is still 199kb and I do not have access to anything to reduce the size. It is as basic as this:

A1 = 2 and A2= 3. A3 = sum of those.
B1 = 4 and B2 = 5. B3 = sum of those. Format all of these cells to currency.
C3 =(A3-B3)/B3 and format the cell percent.

Create a text box
Type in "Region 5"

Create another text box
Use the formula bar to insert "=$A$3"

Create another text box
Use the formula bar to insert "=$C$3"

Copy and paste the data
Change the numbers so the percent is now positive

Create another text box
Use the formula bar to link to the positive percent cell you just created

Now you have one text box with a title, one with a dollar amount, one with a positive percent and one with a negative percent. Insert the code I posted above and you have the most basic sample sheet.

Edit: Then if you run the code as-is, you will see it changes Region 5 to green, $5.00 to red, -44% to red, and the positive percent to green. So I need to know how to write into the code to skip over the text box if it is not a percent, or change that text box to black color. I am not sure why it is classifying the positive dollar value as less than 0 either, that seems odd.

Re: Conditional formatting on multiple PERCENTAGE text boxes only

Bump.

Anyone have any ideas why the code is recognizing positive dollar values as red?

I would actually be ok with a code that just finds negative percents and changes them red. The green part is not necessary. So the code is perfect as-is, except for the part about changing positive dollar values to red. Does anyone understand why it is doing that and how to fix?

Thank you!

Re: Conditional formatting on multiple PERCENTAGE text boxes only

For anyone who wanted something similar, I FIGURED IT OUT! So happy, my first code! It is probably messy and terrible code, but it works! So this code will search the entire sheet for every text box, check to see if it has a "-" for negative percentage, and color the text red. Then it searches for > 0, basically anything else, and colors it black. This way, even though some of my text box titles have a "-" in them, they stay black. And if something was red before the data changes, this way it will change it back to black after running the code after a data change.

Now, if we want to get crazy. After this code, how would I tell it to select individual text boxes to check if they are positive and color text green? So, after this first code is done, I want it to sweep just for Text Box 172, 173, 174, 175, 176, 177, 178, 179, 180, 183, 186 and if they are > 0, then it changes just those to green.

Option Explicit
 
Sub conditionalFormatChange()
     
    Dim TBox As TextBox
    Dim Txt As String
     
    For Each TBox In ActiveSheet.TextBoxes
        Txt = TBox.ShapeRange.TextFrame2.TextRange.Text
        With TBox.ShapeRange.TextFrame2.TextRange.Font.Fill
            If InStr(Txt, "-") Then
                .ForeColor.RGB = RGB(255, 0, 0)
            End If
            Select Case Txt
            Case Is > 0
                .ForeColor.RGB = RGB(0, 0, 0)
            End Select
            .Solid
            .Transparency = 0
            .Visible = msoTrue
        End With
    Next TBox
     
End Sub