Making Target.Address work after Iinserting a Line

Question

I am using:

If Target.Address = "$C$56" Then

    Range("C56").Value = "X"

    Range("D56").Value = ""

    Range("E56").Value = ""

ElseIf Target.Address = "$D$56" Then

    Range("C56").Value = ""

    Range("D56").Value = "X"

    Range("E56").Value = ""

ElseIf Target.Address = "$E$56" Then

    Range("C56").Value = ""

    Range("D56").Value = ""

    Range("E56").Value = "X"

in a VBA subroutine.  This works find until I insert a line in the associated spreadsheet.  The addresses in VBA do not update to the added line and the code stops working.

I have read about assigning a named range and somehow referencing the various cells within the range as a way to fix this issue, but no matter what I have tried, it does not work.  Can anyone help?

Thanks,

Answer 1

BTW

The video in the link below will help you to understand the Intersect method

https://www.youtube.com/watch?v=qTKk0TorMZo

Answer 2

Leo:

Ref, "... I do not know what If Not Application.Intersect(Target, myRange) Is Nothing Then does.  Could you explain?"

It means if the active cell (target) is within the range i.e C56:E59 then...do whatever

The following macro will work as per your requirements

''''*************************************************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Range

Set myRange = Range("Metal_Options")

''' If the active cell is within the myRange

If Not Application.Intersect(Target, myRange) Is Nothing Then

    If Target.Cells.Count > 1 Then Exit Sub  '' Select only one cell

        Range("C" & Target.Row & ":E" & Target.Row).Value = ""   ''' Clear the values

        Target.Value = "X"   ''' Insert X in the active cell

End If

End Sub

'''***********************************************************************************

Regards

Answer 3

HansV:

Is there a way to select an array of cells and then specify individual cells within the range?  See my response to Jeovany.

Thanks,

Answer 4

Intersect(Target, myRange) is the range of cells that is both in Target (the selected range) and in myRange.

If Target is completely disjunct from myRange, Intersect(Target, myRange) is Nothing (empty). So the lines between

If Not Application.Intersect(Target, myRange) Is Nothing Then

and

End If

will only be executed if Target overlaps with myRange.

Answer 5

Jeovany:

This worked great!  And so simple (after I understood what you were doing).

Thank you so much.