having a macro insert a comment

[jhahes]

Could someone please give me some insight on how to do the following.

Right now I am trying to do this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Sheet2.Range("L3").Value >= Sheet2.Range("I3").Value Then
Sheet2.Range("J3").AddComment
Sheet2.Range("J3").Comment.Visible = True
Sheet2.Range("J3").Comment.Text Text:="ORDER MORE!!!:" & Chr(10) &
"" & Chr(10) & "need to order more"
End If

If Sheet2.Range("L3").Value < Sheet2.Range("I3").Value Then
Sheet2.Range("J3").Comment.Delete
End If
End Sub


I am new to macros and understanding them, I also am operating in Excel
2003. What is happening is a debug is happening stating that i need a
block with or something of that nature. I would actually like this to
loop from cell 3:500. I probably am doing this in a very slow and
non-efficient way. I would love some advice for knowledge.

Thank you very much

Josh

 

[Guest]

I dont understand exactly what you want, but I rewrote the sub a bit to
prevent an error statement (id the comment was already there, not a new one
can be crated; if the comment is removed, it cannot be removed again.
the second, use an If...else...End if statement is better than if...end if,
if...end if
(if x >=1 then
do the following
Else
do something else
End if)

The new sub should be copied in the VB-editor on the Sheet2-"microsoft Excel
Object". from there it will automatically run when sheet 2 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
If Range("L3").Value >= Range("I3").Value Then
Range("J3").AddComment
Range("J3").Comment.Visible = True
Range("J3").Comment.Text Text:="ORDER MORE!!!:" & Chr(10) & "" & Chr(10)
& "need to order more"
Else
Range("J3").Comment.Delete
End If
ExitSub:
End Sub