Force Comment when Condition Met. Comment appears on the wrong cell.

[annap0tchi]

Hi,

I have a force comment when the formula value reaches the condition.
the problem is the comment appears on the cell where the value was changed.
can help to force the comment to appear on a specified cell?

this is what I have...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Str As String
Dim cmt As Comment
On Error Resume Next
If Not Intersect(Target, Range("E5:E37")) Is Nothing Then

Range("e3").Formula = "=f3-d3"
If Range("e3").Value > 0.2 Then
Str = InputBox("Your Manhours this month is 20% more than the previous. Comment why.")
With Target
.AddComment
.Comment.Text Text:=Application.UserName & Chr(10) & Str
End With
Set cmt = Target.Comment
With cmt.Shape.TextFrame.Characters.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 8
End With
End If
End If
End Sub

[Kaper]

The code is not well readable (see request below).
But it seems it shall work as expected.
If the comments are moved to the right - you may have merged cells.
Please attach a file (a model one, not the real one with data) where it does not work.
And now important part:


Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here