Run macro automatically when a cell in a dynamic range changes

Hi

my problem is hopefully pretty simple.

my code so far looks like this:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
Run ("Model.Hide")
End If
Application.ScreenUpdating = True
End Sub

which works fine for the range $B$76:$B$114 however the row numbers are
prone to change.

I have setup a table thats location will not change, which will store
the reference start and the reference ends. This is located at B8
(start) and C8 (End).

what i am tring to do is get the information from B8 and C8 to replace
the B76 and B114 respectively.

any ideas?

Hi Glimmer23.

Try:

Private Sub Worksheet_Change(ByVal target As Range)

Dim startCell As Range
Dim endCell As Range
Dim rng As Range

Set startCell = Me.Range(Me.Range("B8").Value)
Set endCell = Me.Range(Me.Range("C8").Value)

Set rng = Range(startCell, endCell)

If Not Intersect(target, rng) Is Nothing Then
Application.ScreenUpdating = False
Run "Model.MyHide"
End If
Application.ScreenUpdating = True

End Sub

I have intentionally changed the name of the called macro from Hide to
MyHide, because 'Hide' as a special significance for VBA and and the
potential subsequent confusion may cause subtle problems.


---
Regards,
Norman



<glimmer23@yahoo.com> wrote in message
news:1123574260.652293.212310@g14g2000cwa.googlegroups.com...
> Hi
>
> my problem is hopefully pretty simple.
>
> my code so far looks like this:
>
> Private Sub Worksheet_Change(ByVal target As Range)
> Application.ScreenUpdating = False
> If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
>

> End If
> Application.ScreenUpdating = True
> End Sub
>
> which works fine for the range $B$76:$B$114 however the row numbers are
> prone to change.
>
> I have setup a table thats location will not change, which will store
> the reference start and the reference ends. This is located at B8
> (start) and C8 (End).
>
> what i am tring to do is get the information from B8 and C8 to replace
> the B76 and B114 respectively.
>
> any ideas?
>

YOUR THE KING - been tring to get that for ages works as just hide -
but ill see if by change both to my hide if it speeds it up

thank you very much for your help

Hi Glimmer23,

> been tring to get that for ages works as just hide -
> but ill see if by change both to my hide if it speeds it up

Using 'MyHide' (instead of 'Hide') will not increase execution speed. It is
offered, purely as advice, to prevent VBA from being confused later when,
perhaps, your code becomes more complex and more difficult to debug.

As a general rule of thumb, it is adivisable to avoid the use of VBA
reserved words in any elective naming process.

---
Regards,
Norman



<glimmer23@yahoo.com> wrote in message
news:1123575808.709592.182660@g14g2000cwa.googlegroups.com...
> YOUR THE KING - been tring to get that for ages works as just hide -
> but ill see if by change both to my hide if it speeds it up
>
> thank you very much for your help
>

Ya didnt relise it was a reserved word - but i know that rule well and
changed it after i made my last post to MyHide because i have run into
reserved name problems before...

thanks again for your help.

sRange = b78 & ":" & C78
If Not Intersect(target, Range(sRange)) Is Nothing Then

--
HTH

Bob Phillips

<glimmer23@yahoo.com> wrote in message
news:1123574260.652293.212310@g14g2000cwa.googlegroups.com...
> Hi
>
> my problem is hopefully pretty simple.
>
> my code so far looks like this:
>
> Private Sub Worksheet_Change(ByVal target As Range)
> Application.ScreenUpdating = False
> If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
> Run ("Model.Hide")
> End If
> Application.ScreenUpdating = True
> End Sub
>
> which works fine for the range $B$76:$B$114 however the row numbers are
> prone to change.
>
> I have setup a table thats location will not change, which will store
> the reference start and the reference ends. This is located at B8
> (start) and C8 (End).
>
> what i am tring to do is get the information from B8 and C8 to replace
> the B76 and B114 respectively.
>
> any ideas?
>

oops, should be

sRange = range("b78").Value & ":" & range("C78")
If Not Intersect(target, Range(sRange)) Is Nothing Then
--
HTH

Bob Phillips

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23pG3NuLnFHA.3628@TK2MSFTNGP10.phx.gbl...
> sRange = b78 & ":" & C78
> If Not Intersect(target, Range(sRange)) Is Nothing Then
>
> --
> HTH
>
> Bob Phillips
>
> <glimmer23@yahoo.com> wrote in message
> news:1123574260.652293.212310@g14g2000cwa.googlegroups.com...
> > Hi
> >
> > my problem is hopefully pretty simple.
> >
> > my code so far looks like this:
> >
> > Private Sub Worksheet_Change(ByVal target As Range)
> > Application.ScreenUpdating = False
> > If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
> > Run ("Model.Hide")
> > End If
> > Application.ScreenUpdating = True
> > End Sub
> >
> > which works fine for the range $B$76:$B$114 however the row numbers are
> > prone to change.
> >
> > I have setup a table thats location will not change, which will store
> > the reference start and the reference ends. This is located at B8
> > (start) and C8 (End).
> >
> > what i am tring to do is get the information from B8 and C8 to replace
> > the B76 and B114 respectively.
> >
> > any ideas?
> >
>
>