Stop (target) Macro From Prompting To Debug

[Kamolga]

Hi,

I have a a target macro that starts with

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Keycells as Range
Set KeyCells = Range("R:R,AF:AF,AT:AT,BH:BH,BV:BV,CJ:CJ,CX:CX,DL:DL")


      If Not Application.Intersect(KeyCells, Range(Target.Address)) _
                   Is Nothing Then
    
        If Range(Target.Address).Offset(0, -4).Value = KeyText Then

etc.

and does a lot of complex (to me) things perfectly as long as I select a value from the lists in the target range.

My problem is that if the user select multiple cells in the range (to delete their values only), I have an error on the last line of the shown code and the debugg screen show up.

I have googled the 3 solutions below without success:

-if target in not a single cell, exit sub
-do not show debugg screen (I tried a few online things and do not feel comfortable with it, where to put it, etc.)
-Click end on the debugg screen (does not seem possible)

Thank you in advance for your help

 

[RickXL]

Hi,

Have you put the Exit Sub in the right place?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Keycells As Range
    Set Keycells = Range("R:R,AF:AF,AT:AT,BH:BH,BV:BV,CJ:CJ,CX:CX,DL:DL")

    If Not Application.Intersect(Keycells, Range(Target.Address)) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Range(Target.Address).Offset(0, -4).Value = KeyText Then
        
        
        
        
        
End Sub

 

[Kamolga]

Hi,

No I did not and the formula was no right either

Thank you very much RickXL, that works great!

 

[RickXL]

No problem - thanks for letting me know.

Regards,