Adding Range of Cell Value to Comments

  • Hi All,


    I searched many forums but did not get any expected results. Below I am sharing my thoughts, hope someone could help me in this context.


    In my worksheet (Worksheet Name Bill), I want Cell "I" will show the value of "W, X & Y"

    My Comments range is I9:I1200

    My data range is "W, X & Y" from 9 to 1200

    In comments the comment will show as " Product ID: "W" value, Product Amount: X value & Product Paid Amount: "Y" Value.

    If No data in "W, X & Y" then no comment.

    If data changes then the comment updates automatically but no need of old values to show in a comment.

    (If the code run in the module will be best for me)


    Hope I am able to describe it. Help on this would be much appreciated.


    Thanks in advance

  • Go to Best Answer
  • Hi, Thanks for your feedback.


    I need a code for doing the above condition to fulfill.

    I found a code & made some changes to the code but failed. (Modified code)

    In the above code, it creates the comment for all the data of range ("W9:Y1200). I want a code that creates comments only for those which have value in the range ("W9:Y1200).

    Can anyone help in this context?


    Hope someone is ready to help which is very appreciating.


    Thanks in Advance.

  • Hi,


    It is not clear on what basis would the comments be updated.


    Pl try the code below. It updates the comments in220728 OZgrid Add comments to cells.xlsm Sheets("Sheet1") from I9 to I1000 using the data from W9:Y1000. If this is not what you want, pl send file with sample data for better coding.


  • Try the following


    First run this code with the sheet needing comments in Column I active

    The above code needs to be run just once to initially add required comments, if any, to Column I


    The code below will update the comment in Column I for any change in Columns W, X or Y in the same row.

    Note this code must be placed in the Sheet Object Module not a standard module

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 2 times, last by KjBox ().

  • Thanks, KjBox for your kind feedback,


    I tried your code but in some cells, it gives the comment but in some cells, it does not though there is value existing in "WXY"

    I can't find the problem in your code. Will you recheck the code?

    Though I tried to establish a code which result is good but it takes a long time to initiate. The code is below.

    I will be happy if you check the below code too.


    Thanks and best regards.

  • Attach a workbook which has just the data you have in columns W, X & Y

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox, I am Attaching a sample data file Test.xlsm with the VBA code in it.

    Please remove the comments in "I" then initiate the command button to see the result.

    Please read the Yellow mark sentences to know my expectation.


    Thanks again.

    • Best Answer

    Try the attached


    Code assigned to the button

    Note this code needs to be run just once and can then be deleted. It will add the comments to all cells in the Table Column 9.

    It will be faster than your code, but even if it takes a few seconds, or even minutes, to run it does not really matter as it never needs to be run again.


    The code below is in the Sheet Object Module and will update the Column 9 comment for any change to Columns 23, 24 or 25

  • Thanks, KjBox for the help and the beautiful code.


    Though I am initially getting a “Run-time 1004 error: Method ‘Sheets’ of object ‘_Global’ failed.”.


    But after changing the ActiveSheet. to Me. the error resolves and the code works like a charm.

    Best wishes to you and really appreciate the help.


    Thanks again.


  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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