End With without With error

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
478
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



Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

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

Code:
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.
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
You need to watch the If...End If structures.

This will compile but I don't know if it'll do what you want.
Code:
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.
Code:
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

Active Member
Joined
Feb 18, 2008
Messages
478
Thanks Colin_L- that worked perfectly, I see what I did wrong. Thanks for the insight on the code and the tutoring.
 

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
478

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Feb 18, 2008
Messages
478
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,865
Messages
5,627,344
Members
416,242
Latest member
Kas O

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top