Formula in comments?

[Manju]

Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards

 

[Guest]

Sorry, no. A comment is just a comment.

It could be done with VBA and the Worksheet_Change() event, at the expense
of a lot of time consumed rewriting your comments in that column every time
something else changed.

 

[Manju]

Thanks. I was delighted to see the result in my worksheet.

But sorry, I just gave an example of column A and B. What are the
changes I have to do if the value is say in column H and Column L.

Also, I want the percent of contenets in H,I,J,and K against column L.
That is respective percents comment in column H,I,J,and K.
Thanks in advance.

 

[Guest]

Use this to replace what you have to deal with columns H-L instead of A-B.
It's not as compact as it could be, but I think you'll find it easier to
modify if you should ever need to this way.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
Dim columnID As String

If Target.Cells.Count > 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column < Range("H1").Column Or _
Target.Column > Range("L1").Column Then
'not in columns H through L
Exit Sub
End If
Application.EnableEvents = False

columnID = "H"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value > 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "I"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value > 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "J"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value > 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "K"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value > 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

Application.EnableEvents = True
End Sub

 

[Manju]

Excellent !
Thank you very much
I would recommend others to look into this solution. May come in handy
Is there a way to apply the code for already existing data?

Happy New Year
Regards
Manju