WordArt formarting

[EinarOSies]

Please I am new to VBA. this code runs the first procedure when I type a number from 1 to 100 but it stops when it is more than 100. Due to that, I had wanted to add an elseif to make the wordart red but it doesn't. Please and also if I add a formula the code does not run.
For correction how can this code work with a formula when the number is between 1 to 100 but will turn red when it is more than 100

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
 If Target.Address = "$A$4" Then
  If IsNumeric(Target.Value) Then

    With Shapes("Rectangle 4").TextFrame2.TextRange.Font.Fill
     .TwoColorGradient msoGradientHorizontal, 1
     .GradientAngle = 270
     .GradientStops(1).Color = RGB(0, 0, 0)
     .GradientStops(1).Position = Target.Value
     .GradientStops(2).Color = RGB(255, 255, 255)
     .GradientStops(2).Position = Target.Value
   
   End With
     End If
ElseIf Target.Address > "100%" Then

    With Shapes("Rectangle 4").TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .BackColor.RGB = RGB(255, 255, 255)
        .TwoColorGradient msoGradientHorizontal, 2
    End With

 
 'End If 'isnumric


   
 End If
End Sub

 

[RoryA]

Target.Address returns the cell address, not its value.

Your code will not work with a formula since a formula recalculation doesn't trigger the Change event - unless you are altering cells on the same worksheet to affect the formula, in which case you need to monitor those cells, not the formula cell.

 

[EinarOSies]

So please then what should do? replace address with value??

 

[EinarOSies]

Please it does not work with value

 

[RoryA]

If you are changing the value in A4, that part (the ElseIf) will never be tested since the first part of your If (ie the check to see if Target.Address = "$A$4") will always be true.

Are you entering 100, or 100%, in the cell? They are completely different values.

 

[EinarOSies]

If you are changing the value in A4, that part (the ElseIf) will never be tested since the first part of your If (ie the check to see if Target.Address = "$A$4") will always be true.

Are you entering 100, or 100%, in the cell? They are completely different values.

100% please

 

[RoryA]

Then you'd need something like:

Private Sub Worksheet_Change(ByVal Target As Range)

   On Error Resume Next
   
   If Target.Address = "$A$4" Then
   
      If IsNumeric(Target.Value) Then
      
         If Target.Value2 <= 1 Then
         
            With Shapes("Rectangle 4").TextFrame2.TextRange.Font.Fill
            
               .TwoColorGradient msoGradientHorizontal, 1
               .GradientAngle = 270
               .GradientStops(1).Color = RGB(0, 0, 0)
               .GradientStops(1).Position = Target.Value
               .GradientStops(2).Color = RGB(255, 255, 255)
               .GradientStops(2).Position = Target.Value
            
            End With
         
         Else
         
            With Shapes("Rectangle 4").TextFrame2.TextRange.Font.Fill
            
               .Visible = msoTrue
               .ForeColor.RGB = RGB(0, 0, 0)
               .BackColor.RGB = RGB(255, 255, 255)
               .TwoColorGradient msoGradientHorizontal, 2
            
            End With
         
         End If ' target.value2 <= 1
      
      End If 'isnumeric
   
   End If

End Sub

 

[EinarOSies]

Then you'd need something like:

Private Sub Worksheet_Change(ByVal Target As Range)

   On Error Resume Next
  
   If Target.Address = "$A$4" Then
  
      If IsNumeric(Target.Value) Then
     
         If Target.Value2 <= 1 Then
        
            With Shapes("Rectangle 4").TextFrame2.TextRange.Font.Fill
           
               .TwoColorGradient msoGradientHorizontal, 1
               .GradientAngle = 270
               .GradientStops(1).Color = RGB(0, 0, 0)
               .GradientStops(1).Position = Target.Value
               .GradientStops(2).Color = RGB(255, 255, 255)
               .GradientStops(2).Position = Target.Value
           
            End With
        
         Else
        
            With Shapes("Rectangle 4").TextFrame2.TextRange.Font.Fill
           
               .Visible = msoTrue
               .ForeColor.RGB = RGB(0, 0, 0)
               .BackColor.RGB = RGB(255, 0, 0)
               .TwoColorGradient msoGradientHorizontal, 2
           
            End With
        
         End If ' target.value2 <= 1
     
      End If 'isnumeric
  
   End If

End Sub

Please thank Rory. It worked but a problem with numbers above hundred. They were appearing black with a gradient of white so I had to tweak it to a gradient of red and white. Once again thank you very much Rory