End With without With error

[rickblunt]

Greetings, I am using the following code to change the colors in a autoshape. Depending on the value the shape changes colors, or is blank if the cell is blank. I am getting the "End With without With" error, and I am confused as to what I am doing wrong, I have a With and an End With statement there... - thank you for any help. RB

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$46" Then
    'Change autoshape color to red depending upon cell value, or blank of no value is entered.
  With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
    If Target.Value = "" Then
        .SchemeColor = 1
        Else
    If Target.Value >= 1 And Target.Value <= 421 Then
        .SchemeColor = 10
        Else
       If Target.Value >= 422 Then .SchemeColor = 50
    End If
  End With

 

[Colin Legg]

Hi,

I think you're looking for one If...ElseIf...End If block within your With ... End With block:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$46" Then
    
        'Change autoshape color to red depending upon cell value, or blank of no value is entered.
        With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
            If Target.Value = "" Then
                .SchemeColor = 1
            ElseIf Target.Value >= 422 Then
                .SchemeColor = 50
            ElseIf Target.Value >= 1 Then
                .SchemeColor = 10
            Else
                'it must be less than 1
            End If
        End With
    
    End If
    
End Sub

By ordering the conditions carefully, we can drop the requirement of the And in your initial code.

 

[Norie]

You need to watch the If...End If structures.

This will compile but I don't know if it'll do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$46" Then
        'Change autoshape color to red depending upon cell value, or blank of no value is entered.
        With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
            
            If Target.Value = "" Then
                .SchemeColor = 1
                Else
                    If Target.Value >= 1 And Target.Value <= 421 Then
                        .SchemeColor = 10
                    Else
                        If Target.Value >= 422 Then .SchemeColor = 50
                    End If
            End If
        End With
        
    End If
End Sub

Personally I would probably go for Select...case.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$46" Then
        'Change autoshape color to red depending upon cell value, or blank of no value is entered.
        With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
        
            Select Case Target.Value
                Case ""
                    .SchemeColor = 1
                Case 1 To 421
                    .SchemeColor = 10
                Case Is >= 422
                    .SchemeColor = 50
            End Select
            
        End With
        
    End If
End Sub

 

[rickblunt]

Thanks Colin_L- that worked perfectly, I see what I did wrong. Thanks for the insight on the code and the tutoring.

 

[rickblunt]

Thanks Norie for the help, I am still learning all of this stuff. Why do you perfer the "Select Case" method, is it less to type or easier to manage or something like that?

 

[Colin Legg]

Hi Rick,

Yes, users generally find it easier to read/write. The catch is that it runs slightly slower than If.. ElseIf.. End If block equivalent.

 

[rickblunt]

Cool, I will keep that in mind (and add it to my coding book), in this case I am working with 200+ autoshapes so I may not use it on this one, but I will definitley try it out on my next project. Thanks for all of the help. RB