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.


    Private Sub Worksheet_Change(ByVal Target As Range)


    Code
    1. [COLOR=#FF0000]If Not Intersect(Target, Range("d16")) Is Nothing Then
    2. msgbox "Hello!"
    3. End If[/COLOR]


    End if


    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 "Like" icon ...just below...in the bottom right corner...:)

  • Hello again,


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


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


    Hope this will help

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