Adding Comment To Range Of Cells by VBA

[payam27]

Hello

I Need To Add Comments To Range of Cells Automatically when I Select That Sheet .
The Range have Two or more column not just one .First range have value of Comments and need to put them as a Comment for second Range.

I find simple way to do that but just for one cell

Sub AddComments()

[m5].ClearComments
[m5].AddComment
[m5].Comment.Text Format([J5])
[m5].Comment.Visible = False
[m5].Comment.Shape.TextFrame.AutoSize = True

End Sub

In this simple code I put value of Cell J5 as a Comment for Cell M5. i Could Repeat this but Cells are so many and that code will be long and not professional.

More Details:

My Sheet Name is : Matches
Cells range contain value of Comments are HM4:HQ203 ( 5 columns HM , HN , HO , HP , HQ From Cell 4 to 203 Each one)
Value of These Cells must put as Commet of Cells Range I4:M203 ( 5 Columns I , J , K , L , M From Cell 4 to 203 )

HM4:HQ203 =======> I4:M203

so for example:

Cell I4 will get value of HM4 as a comment ,
Cell J203 Will get value of HN203 as a comment,
Cell L100 will get Value of HP100 as a comment,
Cell M5 will get value of HQ5 as a comment.
.....
I need it be automated every time those cells changes make those comments changes too , i dont want use button and asign macro to run this macro.

Probably I need a loop thing like 2 Inside For Next to do this but I'm not good in VBA yet.

Thanks

[mehmetcik]

Please Login or Register  to view this content.

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

[payam27]

Hello Mehmet

Thanks for reply

I put your first code in a Module and your second code as you said in a sheet view code section
Then i did run first macro and it worked , )) but when i changed cells the first code didnt automatically update the new comments.
i dont know maybe i put first code in wrong place or something i missed to do and do i need to run atleast one time the first code ?

so
1-The first code do what i need it adds comments to cells range. but i need to run it once , can you make it auto ?

2- The second code didnt work, I changed the values but nothing happend.

I guess there is small thing must be correct or something i missed to do

waiting for you and thanks again


New

I solvevd No.1 by putting codes between this sub

Private Sub Workbook_Open()
.
.

end sub

now just No.2 ,

[mehmetcik]

Sample file attached

[payam27]

Hi

Thanks

Your Code is correct i did test it and its ok ,but it doesn't work on my file ,and I find the reason
if i directly change the cell value it will works , but in my file each cells have a formula .
Cell HM4 (and all cells ) itself value depends on formula like this HM4=Vlookup.....
so when cells value changes based on that formula it doesnt trigger the second macro to update comments again

[mehmetcik]

Ahh.

in that case you may have to change the first MaCRO TO:

Please Login or Register  to view this content.

And the second Macro to

Please Login or Register  to view this content.

[payam27]

hi
The new code is ok but my sheet is full of other cells with formula so the sheet will be always in update loop.
so i prefer to do it manually by a button .
the main problem solved and that was first code you gave.
Thanks again