How can I change this Code from being just Column G to Columns G:M

[tonywatsonhelp]

Hi Everyone,

The code below works great, but I'd like to expand its target from Column G to Columns G:M
any ideas the best way to do this?

Thanks

Tony

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
LastR = Sheets("Weekly Data Input").Cells(Rows.Count, "F").End(xlUp).Row
    With Application
        If Target.Address = Range("G" & LastR).Address Then
            .OnKey "{TAB}", Me.CodeName & ".GoBack"
            .OnKey "{ENTER}", Me.CodeName & ".GoBack"
            .OnKey "{Down}", Me.CodeName & ".GoBack"
            .OnKey "~", Me.CodeName & ".GoBack"
        Else
            .OnKey "{TAB}"
            .OnKey "{ENTER}"
            .OnKey "{Down}"
            .OnKey "~"
        End If
    End With
Shapes.Range(Array("Group 3")).Top = ActiveWindow.VisibleRange(2, 3).Top
   
End Sub


Private Sub GoBack()
LastR = Sheets("Weekly Data Input").Cells(Rows.Count, "F").End(xlUp).Row
Range("G" & LastR).Select
End Sub

 

[daverunt]

Cancel that - retrying

 

[daverunt]

Try again:

You can use the Intersect

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("G" & LastR & ":M" & LastR)) Is Nothing Then


        MsgBox Target.Address  'your WITH code here

   End If


Shapes.Range(Array("Group 3")).Top = ActiveWindow.VisibleRange(2, 3).Top

End Sub

 

[tonywatsonhelp]

HI Daverunt,
This is a good idea but the second part

[LEFT][COLOR=#333333][FONT=monospace]Private Sub GoBack()
LastR = Sheets("Weekly Data Input").Cells(Rows.Count, "F").End(xlUp).Row
Range("G" & LastR).Select
End Sub[/FONT][/COLOR][/LEFT]

Needs to change from G to whatever column they where in? any ideas????
Tony

 

[daverunt]

Oh.

See if this works for you - otherwise I'm stumped
Create a public variable for the column as below so it is accessible outside of the Sub.
You can probably do the same for LastR

Public Col As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("G" & LastR & ":M" & LastR)) Is Nothing Then
Col = Split(ActiveCell(1).Address(1, 0), "$")(0) 'Gets the column letter

WITH Code~~~~
End If

Private Sub GoBack()

LastR = Sheets("Sheet4").Cells(Rows.Count, "F").End(xlUp).Row
Range(Col & LastR).Select

End Sub

 

[daverunt]

Tsk. Running on one cell today.

Use the target address.

Col = Split(Target.Address(1, 0), "$")(0)

 

[tonywatsonhelp]

Hi Daverunt,

This is great, I can use this to do what I need thank you.
Tony

 

[daverunt]

Glad it helps.