Defining Target Range

[Flenley]

I am trying to teach myself VBA and am having trouble below defining the range. Everything is working except for the fact that any change on the sheet is adding the comment. I only want the code to run if cell a1 = 3 and I don't know where I am going wrong.

Any help would be much appreciated!

Sub worksheet_change(ByVal Target As Range)
If Target.Range("a1") = 3 Then
If MsgBox("How are you", vbOKCancel) = vbOK Then
Range("c4").Select
ActiveCell.AddComment "Outbound Only"
End If
End If

End Sub

 

[Damon Ostrander]

Hi Flenley,

Target already IS a range. You simply need to check whether it is cell A1 and its value:

Sub worksheet_change(ByVal Target As Range)
If Target.Address = [A1].Address AND Target.Value = 3 Then
If MsgBox("How are you", vbOKCancel) = vbOK Then
Range("c4").Select
ActiveCell.AddComment "Outbound Only"
End If
End If

End Sub

 

[Emily]

Hi
If target A1 has been changed more than one, error code 1004 appears.
Is it required to add "On Error Resume Next" on top or any alternative suggestion?

 

[Flenley]

Thanks Damon Ostrander. It works well now.

Angus

 

[Ivan F Moala]

On 2002-08-29 21:18, Emily wrote:
Hi
If target A1 has been changed more than one, error code 1004 appears.
Is it required to add "On Error Resume Next" on top or any alternative suggestion?

Hi Emily
Welcom to the Board
Yes you are correct the On Error Resume Next
should be added to this incase Comment has
already being created....If your comment is
changing then you also need to Delete the old
comment and then Add the new comment..