Combine 2 super easy worksheet_change events

  • Hey guys, today for the first time in my life I faced a situation when I need VBA. I've found a code that works for me (thanks Google!) but the thing is that I need to apply it 2 times (change in column 7 registered in column 8 and change in column 9 registered in column 10) on he same worksheet. As far as I understand I need to combine it into 1 sub. I've tried several variants but all of them end up with an error. ;( Pleaaaaase I need your help so much - how to combine it? Thank you in advance


  • Hello and Welcome to the Forum:)


    You can test following modification

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Intersect(Target, Union(Range("G:G"), Range("I:I"))) Is Nothing Then Exit Sub
    3. If Target.Count > 1 Then Exit Sub
    4. With Target.Offset(0, 1)
    5. .Value = Now
    6. .NumberFormat = "MM/DD/YYYY"
    7. End With
    8. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Using a Select case would be easier to manage. You can easily add columns to the code or change them.


    Note use CountLarge because it is possible to create an overflow error using Count in modern spreadsheets


  • OMG it works!!


    Carim, thank you so so much! You saved me hours of suffering :D

    Glad to hear this could help you out :)


    Thanks a lot for your Thanks ...AND for the Like :thumbup:


    By the way ... there is no need to be ' Desperate ' ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)