How can I use a range for target address?

  • I have a project and a column is formatted for Date like (“A1:A200”) or more
    I like when I select any cell from A1 to A 200 to have my calendar form to pop up so that I can pick the desired date and pass it to that cell.
    So far I can do this for a single cell or using the OR statement on my code below I can use it for a few more cells
    Any help please?
    Thanks
    Teoant



    # Private Sub Worksheet_SelectionChange(ByVal Target as Range)
    If Target. Address = "$B$6" Then


    ‘If Target.Address = "$B$6" Or Target.Address = "$B$10" Or Target.Address = "$B$30" Then

    'Is it possible to use a single Cell from a range like ("$B$5:$B$200") for Target. Address
    'Instead of a single cell address???


    UserForm2.Show
    End If
    End Sub

  • Re: How can I use a range for target address?


    Hi teoant...


    not sure if this is the most efficient way, but does the trick for me...

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. If Target.Column = 1 And (Target.Row >= 1 AND Target.Row <= 10) Then
    3. MsgBox "Hi"
    4. End If
    5. End Sub


    Looks at the range A1:A20

    D ;)


    :sailboat: [SIZE=1]Better a bad day on the water than a good day in the office[/SIZE]

  • Re: How can I use a range for target address?


    Hi,


    Here is one way to only do something for cells B5:B1

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. If Not Application.Intersect(Target, Range("B5:B10")) Is Nothing Then
    3. MsgBox Target.Address
    4. End If
    5. End Sub

    [h4]Cheers
    Andy
    [/h4]

  • Re: How can I use a range for target address?




    Hi Andy
    why can i not use a third range?:
    Target, Range("C3:C8", "E3:E8") - this works
    Target, Range("C3:C8", "E3:E8", "F15:F16") - this does not work