cell change

[ranswert]

I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("drw1inforng")
If .Value <> "" Then
MsgBox ("")
End If
End With
stoppit:
Application.EnableEvents = True

End Sub

'drw1inforng' is a named range on my sheet. When I make a change in this
range, nothing happens. What am I doing wrong here?
Thanks

 

[Mike H]

What would you like to happen?

The code executes when any cell on the sheet changes but your don't tell it
to do anything. Here's a guess that does something and may get you going:-

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
For Each c In Range("drw1inforng")
If c.Value <> "" Then
MsgBox c.Value
End If
Next
stoppit:
Application.EnableEvents = True
End Sub

Mike

 

[ranswert]

I am looking for a way to run a macro when a cell in a range is changed. I
used the code that you suggested and each time a cell is changed, it goes
thru all the cells in that range. i only need it to work on the cell that is
change within a range of cells. How do I do this?
Thanks

 

[SparkyUK]

Works fine for me! Good luck.

 

[SparkyUK]

Try the UNION method with Target and drw1inforng ranges

 

[ranswert]

Can you give me an example?

Try the UNION method with Target and drw1inforng ranges

 

[SparkyUK]

Sorry should have used INTERSECT method:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngUnion As Range

Set rngUnion = Application.Intersect(Target, Me.Range("drw1inforng"))

If Not (rngUnion Is Nothing) Then
On Error GoTo stoppit
Application.EnableEvents = False
If rngUnion.Value <> "" Then MsgBox ("Not blank")
End If

stoppit:
Application.EnableEvents = True

End Sub

-----

 

[ranswert]

Thanks I'll give it a try

Sorry should have used INTERSECT method:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngUnion As Range

Set rngUnion = Application.Intersect(Target, Me.Range("drw1inforng"))

If Not (rngUnion Is Nothing) Then
On Error GoTo stoppit
Application.EnableEvents = False
If rngUnion.Value <> "" Then MsgBox ("Not blank")
End If

stoppit:
Application.EnableEvents = True

End Sub

 

[Mike H]

will this doit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("drw1inforng")) Is Nothing Then
MsgBox Target.Value
End If
End Sub

Mike