Run Private Sub Worksheet_Change(ByVal Target As Range) Macro

  • I am doing macro. However, it is running but not working. Not sure where I went wrong.


    I tried referring to below link but still doesn't work for me.


    Kindly advise. Thank you.

    HTML
    https://www.ozgrid.com/forum/forum/help-forums/excel-general/108851-how-do-i-run-a-private-sub-worksheet_change-byval-target-as-range-macro


  • Hello,


    To clarify things a little :


    1. The event macro below ... needs to be stored in the respective Sheet Module ...in your case Sheet1



    2. The standard macro below ... needs to be stored in a standard Module ...

    Code
    Sub Hello()    Run "Sheet1.Worksheet_change", Range("A1")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 :)

  • Hello,


    Much better than pictures ... which are totally inert objects ...


    why don't you attach your file ...???

    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 :)

  • Hi, it works. Thank you. However, when I try to run the same macro to a similar file, i can see the macro but when run it doesn't highlight. Is there any additional steps to do? I tried the create new macro for the file and copied the codes for sheet and module.

  • Hi,


    Glad to hear the macro is working as expected ...:wink:


    Thanks for your thanks ... AND for the Like ...:smile:




    If I could try to guess what you are doing in order to adapt the macro to your actual file ...


    1. Your data is either copied or downloaded from the mainframe ...


    2. As a result, you are getting a very strange structure showing data every 8 columns ...


    3. AND ... All your TRUE / FALSE cells need to be cleaned with an individual F2 Enter or with TextToColumns ...


    Hope this clarifies

    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 :)

  • Do not hesitate to come back to the Forum ... should you face more difficulties ...

    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 :)

  • Hi [USER="31712"]Carim[/USER], can I use the same codes for different sheets? e.g.
    [ATTACH=JSON]{"alt":"Click image for larger version Name:\thi.jpeg Views:\t1 Size:\t54.6 KB ID:\t1210220","data-align":"none","data-attachmentid":"1210220","data-size":"full","title":"hi.jpeg"}[/ATTACH]

  • Hello,


    Do you mean you need a macro to correct all the Sheets ....with the exception of the Sheet named ' Summary ' ... ?


    If the answer to this question is : Yes ...


    Please confirm all these sheets have exactly the same structure ...:wink:

    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 :)

  • Hi, I have been trying but couldn't. Pls help.


    p.s. I managed to do it. However, for the correction macro, is it possible to run it for all worksheets?. As the other macro(for highlighting is auto updated), but correction is not


    And sorry one more question..


    in this code:
    Application.Calculation = xlCalculationManual
    For i = 2 To 7820
    For j = 8 To 48 Step 8
    Run "Sheet3.Worksheet_change", Cells(i, j)
    Next j
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


    It looks for 'False' cells in every 8th column, and do the highlighting on the 7th column.


    Can it look at every 6th and 8th column for the false cells and do the highlighting on the column before the false cell?

  • Hello,


    You have to agree with the three following remarks :


    1. You keep on changing your worksheet structure ...



    2. You are not answering the questions listed in message # 11



    3. Your very last request is not clear ..."do the highlighting on the column before the false cell"
    since these cells do not contain the same type of information ...

    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 :)

  • Hi carim, sorry for the trouble
    1. Yes I did changed the structure abit. However, the structure somewhat similar. every 8 columns repeats the same structure.
    2. Yes, correct all worksheets exclusdng summary. All the sheets have same structure.
    3. If I change the cells to be like below, Can the look at the both 6th and 8th column for FALSE value?
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"hi4.jpeg","data-attachmentid":1210269}[/ATTACH]

Participate now!

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