End With without With error

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
609
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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:
Upvote 0
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
 
Upvote 0
Thanks Colin_L- that worked perfectly, I see what I did wrong. Thanks for the insight on the code and the tutoring.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,487
Messages
6,130,945
Members
449,608
Latest member
jacobmudombe

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
Back
Top