Comment Column

[dheeraj1688]

I want to create column/cell for comment ,when user edit or write in column/cell his name and content will be highlight with color. then next user insert his/her comment in same cell(different color will be used to differentiate the comments).

Please suggest suggestion/macro to do this.
Thanks in advance

 

[Hui]

Dheeraj1688

Firstly,Welcome to the Chandoo.org Forums

Why not just use the existing Cell Add Comment feature
It lets you add comments and will add the username automatically if it is a new user
You can easily add a UDF to retrieve the Cell's comments to a cell if you want

Copy the following code into a code module in VBA (Alt+F11)

Function Retrieve_Comments(Target As Range) As String
  Retrieve_Comments = Target.Comment.Text
End Function

to use simply type
=Retrieve_Comments(F4)

where F4 is the cell with the comments

 

[dheeraj1688]

Dheeraj1688

Firstly,Welcome to the Chandoo.org Forums

Why not just use the existing Cell Add Comment feature
It lets you add comments and will add the username automatically if it is a new user
You can easily add a UDF to retrieve the Cell's comments to a cell if you want

Copy the following code into a code module in VBA (Alt+F11)

Function Retrieve_Comments(Target As Range) As String
  Retrieve_Comments = Target.Comment.Text
End Function

to use simply type
=Retrieve_Comments(F4)

where F4 is the cell with the comments

hello sir,
Thank you for reply ..But can i add multiple comments in single cell with different colour. I attached a picture what i'm trying to do in single cell.MS comment feature may be it allow only one comment and every time if i edit the comment i have to write formula again.

 

[Nebu]

Guess this is what you are looking for. Change any cell in Sheet 1 it will give the user name changed value user name and date in cell C of sheet1. You will have to specify which color for which username in the code to change the color I have given red as default...

 

[dheeraj1688]

Guess this is what you are looking for. Change any cell in Sheet 1 it will give the user name changed value user name and date in cell C of sheet1. You will have to specify which color for which username in the code to change the color I have given red as default...

hi Nebu thanks for reply and help.i am happy with what u provide me.Actually i'm new to excel and i dn't knw where to change the code..
Now problem is what ever i write in sheet1 ,cell (C2) get update with frequent change in sheet.I want to hold previous values also and new values with different color.I don't know the users because that will be move between many people.

 

[Nebu]

Hi:
Please find the attached.
Whatever change u make in sheet 1 will be captured in the log sheet.

guess this will serve your purpose ...

 

[dheeraj1688]

Hi:
Please find the attached.
Whatever change u make in sheet 1 will be captured in the log sheet.

guess this will serve your purpose ...

hi, again thank u for support ,but this is not possible as sheet contain too much and i'm targeting to a single column (comment Column).I'm happy with the previous code can u make it or (tell me ) for a single cell/column as currently it's updated when i click anywhere in sheet. Plz. help in making it for only single cell or column.So, when i double click on that particular cell macro activate and punch the username and time.

 

[Nebu]

Hi:

I have made the changes assuming that column E is your comment column. It is difficult to color code the changes, as you do not know the users beforehand. However, you can anyways distinguish them based on their user name so color coding may not add any value. Please find the attached.

Thanks