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 "Thumbs Up" icon, below, in the bottom right corner:)

  • 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 "Thumbs Up" 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!