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

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" wrote:

> 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

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

"Mike H" wrote:

> 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" wrote:
>
> > 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

Works fine for me! Good luck.

"ranswert" wrote:

> 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

Try the UNION method with Target and drw1inforng ranges

"ranswert" wrote:

> 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
>
> "Mike H" wrote:
>
> > 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" wrote:
> >
> > > 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

Can you give me an example?

"SparkyUK" wrote:

> Try the UNION method with Target and drw1inforng ranges
>
> "ranswert" wrote:
>
> > 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
> >
> > "Mike H" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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

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" wrote:

> Can you give me an example?
>
> "SparkyUK" wrote:
>
> > Try the UNION method with Target and drw1inforng ranges
> >
> > "ranswert" wrote:
> >
> > > 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
> > >
> > > "Mike H" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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

Thanks I'll give it a try

"SparkyUK" wrote:

> 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" wrote:
>
> > Can you give me an example?
> >
> > "SparkyUK" wrote:
> >
> > > Try the UNION method with Target and drw1inforng ranges
> > >
> > > "ranswert" wrote:
> > >
> > > > 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
> > > >
> > > > "Mike H" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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

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

"ranswert" wrote:

> 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
>
> "Mike H" wrote:
>
> > 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" wrote:
> >
> > > 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