Automatically insert and\or change comments based on vlookup of cell value

Hi Everyone,

I am after a code that will insert a comment into a cell based on a vlookup of the cell value.

I have three columns (B, I and P) with formulas using data from other columns (C:H, J:O and Q:V respectively) in the same sheet. I want a comment automatically inserted in each cell in columms B, I and P based on a vlookup of the cell value using defined name ranges. Eg, looking at the attached pdf Test Sheet.pdf, B4 comment would be "Rank 4 Description" by using vlookup(B4,"Tier_One",2,0), I4 comment would be "Rank 2 Description" by using vlookup(I2,"Tier-Two",2,0), etc.

The data used for the formulas will change often, therefore, the comments will also need to be updated as and when the cell value changes.

I have been trying to modify the below code (I didn't write this code) to suit but have no real idea of what I am doing:-

Please Login or Register  to view this content.

Any help will be greatly appreciated.

Thanx

Hi, welcome to the forum

1st, please use code tags when posting code. I have inserted them for you - this time. (see my foot-notes on how to add them)
2nd, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

3rd, if you are already using vlookup(), why not continue to use that to insert the comment, instead of using VBA?

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

And I just realised that when you said comment, you actually meant to add a comment to a cell, not have the cell return text, sorry. vlookup() wont do that for you