Using If Not Intersect(Target..... in Standard Code Module

    Hi All


    I am using the following code inserted into a worksheet_change event with no problem... all working as expected, however, as I am wanting to move the code highlighted in red into a standard module as I will be checking hundreds of cells and the code required exceeds the permitted size allowed. My question is... how do I write the 'if not intersect......' code in a standard module, as i need to refer the code to a specific worksheet, lets say 'Sheet1'


    Any help/pointers would be very much appreciated.




    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d16")) Is Nothing Then
msgbox "Hello!"
End If
End Sub


    Regards
    Martin

    Hello,


    In an event macro ... such as your initial macro ... the word Target is a dedicated word which instructs Excel to refer to the cell selected by the user ...


    When you design a standard macro, you do need to define your ranges for the macro to operate as you wish ...


    Hope this will help

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

    Thanks Carim, can you give me a quick example on how i would write this in a standard module.


    Sorry I'm struggling with this one!


    Regards
    Martin

    Hello again,


    If you are sticking to your current macro ... you could test the following standard macro :


    Code
    Sub TestMpParker()
If Not Intersect(ActiveCell, Range("D16")) Is Nothing Then MsgBox "Hello again !!!"
End Sub


    Hope this will help

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

    Quote from mpparker

    Thanks again Carim, working now!


    Glad you could fix your problem ... ;)


    Thanks ... for your Thanks ..AND for the Like ... :)

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login