title: Text Box Conditional Formatting - Excel VBA
body: <p>I have a dashboard excel spreadsheet that has textboxes. In each text box is a formula pointing to a cell where a formula is applied to the raw data.</p>
  
  <p>I am looking for a way to conditionally format the text boxes depending on either the value in the text box or the raw data behind if that's easier. Essentially if a textbox has a value over one I would like the font to be green if it is under I would like it to be red. I've had a hard time doing this so far and would appreciate anyones help. Below is my code so far but it wont run. I am a bit of a novice when it comes to VBA.</p>
  
  <pre><code>Sub Test_Change_Text()
  
  If ActiveSheet.Range("A1").Value &gt; ActiveSheet.Range("B1").Value Then
  ActiveSheet.Shapes.Range(Array("textbox 1")).Select
   With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font.Fill
    .Visible = msoTrue
  .ForeColor.RGB = RGB(255, 0, 0)
  Else
  With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font.Fill
    .Visible = msoTrue
  .ForeColor.RGB = RGB(255, 0, 0)
  End With
  End With
  
  End Sub
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/AQhxb.png" alt="screenshot dashboard"></p>
  
  <p>Update: The code below is my final working code. This allows for three constraints. </p>
  
  <pre><code>Sub ChangeText()
      Dim shap As Shape
  
      For Each shap In Sheets("Output").Shapes
          If shap.Type = msoTextBox Then
              If IsNumeric(shap.TextEffect.Text) Then
                  If shap.TextEffect.Text &gt;= 3 Then
                      shap.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 255, 0)
                  Else
                      If shap.TextEffect.Text &lt;= -3 Then
                          shap.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
                      Else
                          shap.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
                      End If
                  End If
  
              End If
          End If
      Next shap
  
      MsgBox "Done"
  End Sub
  </code></pre>