Hi,

I might be asking for too much here, but what I am trying to do is to set up
a spreadsheet which contains conditional formatting for more than 3
conditions (not too hard). However, I also want to have each condition
reference pairs of cells elsewhere in the worksheet that will define the two
parameters between which the input value will need to fall in order to
satisfy the condition criteria (does that make sense?)

I have just about got to grips with the conditional formatting for more than
3 conditions, however it is referencing these cells in the 'Case' part of the
VBA code that has me beaten. The reason that I don't want to simply put the
parameters into the VBA code is that they are subject to change on an annual
basis. As such, it would be preferable to simply update the pairs of values
the sheet that the condition makes reference to, in order to avoid having to
amend vast numbers of conditions in the VBA code on an annual basis.

I gather that Excel 2007 no longer has the restriction of 3 conditional
formats, however the organisation where I work are unlikely to be moving to
this anytime soon, hence my desire to find an answer to this problem. It
should also mean that anyone could update the sheet annually for parameter
changes, rather than needing to have an aptitude for Excel programming to
achieve this.

I'm hoping rather than expecting, but if anyone has any ideas, they will be
most gratefully received.

Thanks,

Richard

Hi Richard
Rather than try and re-create the code you have already, post it here and
someone can then amend to suit your needs

--
Cheers
Nigel



"Dickie Worton" <(E-Mail Removed)> wrote in message
news3DE1336-32E3-4B3D-8342-(E-Mail Removed)...
> Hi,
>
> I might be asking for too much here, but what I am trying to do is to set
> up
> a spreadsheet which contains conditional formatting for more than 3
> conditions (not too hard). However, I also want to have each condition
> reference pairs of cells elsewhere in the worksheet that will define the
> two
> parameters between which the input value will need to fall in order to
> satisfy the condition criteria (does that make sense?)
>
> I have just about got to grips with the conditional formatting for more
> than
> 3 conditions, however it is referencing these cells in the 'Case' part of
> the
> VBA code that has me beaten. The reason that I don't want to simply put
> the
> parameters into the VBA code is that they are subject to change on an
> annual
> basis. As such, it would be preferable to simply update the pairs of
> values
> the sheet that the condition makes reference to, in order to avoid having
> to
> amend vast numbers of conditions in the VBA code on an annual basis.
>
> I gather that Excel 2007 no longer has the restriction of 3 conditional
> formats, however the organisation where I work are unlikely to be moving
> to
> this anytime soon, hence my desire to find an answer to this problem. It
> should also mean that anyone could update the sheet annually for parameter
> changes, rather than needing to have an aptitude for Excel programming to
> achieve this.
>
> I'm hoping rather than expecting, but if anyone has any ideas, they will
> be
> most gratefully received.
>
> Thanks,
>
> Richard

Nigel,
Thanks for the tip, I'll document what I have and post it.
Regards,
Dickie

"Nigel" wrote:

> Hi Richard
> Rather than try and re-create the code you have already, post it here and
> someone can then amend to suit your needs
>
> --
> Cheers
> Nigel
>
>
>
> "Dickie Worton" <(E-Mail Removed)> wrote in message
> news3DE1336-32E3-4B3D-8342-(E-Mail Removed)...
> > Hi,
> >
> > I might be asking for too much here, but what I am trying to do is to set
> > up
> > a spreadsheet which contains conditional formatting for more than 3
> > conditions (not too hard). However, I also want to have each condition
> > reference pairs of cells elsewhere in the worksheet that will define the
> > two
> > parameters between which the input value will need to fall in order to
> > satisfy the condition criteria (does that make sense?)
> >
> > I have just about got to grips with the conditional formatting for more
> > than
> > 3 conditions, however it is referencing these cells in the 'Case' part of
> > the
> > VBA code that has me beaten. The reason that I don't want to simply put
> > the
> > parameters into the VBA code is that they are subject to change on an
> > annual
> > basis. As such, it would be preferable to simply update the pairs of
> > values
> > the sheet that the condition makes reference to, in order to avoid having
> > to
> > amend vast numbers of conditions in the VBA code on an annual basis.
> >
> > I gather that Excel 2007 no longer has the restriction of 3 conditional
> > formats, however the organisation where I work are unlikely to be moving
> > to
> > this anytime soon, hence my desire to find an answer to this problem. It
> > should also mean that anyone could update the sheet annually for parameter
> > changes, rather than needing to have an aptitude for Excel programming to
> > achieve this.
> >
> > I'm hoping rather than expecting, but if anyone has any ideas, they will
> > be
> > most gratefully received.
> >
> > Thanks,
> >
> > Richard
>
>
>

OK Nigel (& anyone else!)

Here is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)



Dim icolor As Integer

If Not Intersect(Target, Range("D8:E32")) Is Nothing Then

Select Case Target

Case 0 To 90

icolor = 10

Case 90 To 100

icolor = 43

Case 100 To 110

icolor = 6

Case 110 To 120

icolor = 44

Case 120 To 200

icolor = 3


Case Else

'Whatever

End Select

Target.Interior.ColorIndex = icolor

End If

What I would now like to do is replace the 'Case 0 to 90' (for example),
with a reference to five pairs of cells (D2:E2, D3:E3, etc), so that if the
value falls between those in either of these five pairs that the cell will be
formatted accordingly.

As mentioned in my original post, this would mean that as the target ranges
for each pair change annually it will only require me to change the values in
these five pairs of cells to effect changes to the conditional formatting,
rather than to wade through acres of VBA code.

Hope this helps and that in turn someone can help me.

Many thanks,

Richard


"Dickie Worton" wrote:

> Nigel,
> Thanks for the tip, I'll document what I have and post it.
> Regards,
> Dickie
>
> "Nigel" wrote:
>
> > Hi Richard
> > Rather than try and re-create the code you have already, post it here and
> > someone can then amend to suit your needs
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Dickie Worton" <(E-Mail Removed)> wrote in message
> > news3DE1336-32E3-4B3D-8342-(E-Mail Removed)...
> > > Hi,
> > >
> > > I might be asking for too much here, but what I am trying to do is to set
> > > up
> > > a spreadsheet which contains conditional formatting for more than 3
> > > conditions (not too hard). However, I also want to have each condition
> > > reference pairs of cells elsewhere in the worksheet that will define the
> > > two
> > > parameters between which the input value will need to fall in order to
> > > satisfy the condition criteria (does that make sense?)
> > >
> > > I have just about got to grips with the conditional formatting for more
> > > than
> > > 3 conditions, however it is referencing these cells in the 'Case' part of
> > > the
> > > VBA code that has me beaten. The reason that I don't want to simply put
> > > the
> > > parameters into the VBA code is that they are subject to change on an
> > > annual
> > > basis. As such, it would be preferable to simply update the pairs of
> > > values
> > > the sheet that the condition makes reference to, in order to avoid having
> > > to
> > > amend vast numbers of conditions in the VBA code on an annual basis.
> > >
> > > I gather that Excel 2007 no longer has the restriction of 3 conditional
> > > formats, however the organisation where I work are unlikely to be moving
> > > to
> > > this anytime soon, hence my desire to find an answer to this problem. It
> > > should also mean that anyone could update the sheet annually for parameter
> > > changes, rather than needing to have an aptitude for Excel programming to
> > > achieve this.
> > >
> > > I'm hoping rather than expecting, but if anyone has any ideas, they will
> > > be
> > > most gratefully received.
> > >
> > > Thanks,
> > >
> > > Richard
> >
> >
> >

If Not Intersect(Target, Range("D8:E32")) Is Nothing Then


Select Case Target

Case Range("D2").Value To Range("E2").Value

icolor = 10

Case Range("D3").Value To Range("E3").Value

icolor = 43

Case Range("D4").Value To Range("E4").Value

icolor = 6

Case Range("D5").Value To Range("E5").Value

icolor = 44

Case Range("D6").Value To Range("E6").Value

icolor = 3


Case Else

'Whatever

End Select

Target.Interior.ColorIndex = icolor

End If

--
Regards,
Tom Ogilvy

"Dickie Worton" wrote:

> OK Nigel (& anyone else!)
>
> Here is the code I have so far:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>
>
> Dim icolor As Integer
>
> If Not Intersect(Target, Range("D8:E32")) Is Nothing Then
>
> Select Case Target
>
> Case 0 To 90
>
> icolor = 10
>
> Case 90 To 100
>
> icolor = 43
>
> Case 100 To 110
>
> icolor = 6
>
> Case 110 To 120
>
> icolor = 44
>
> Case 120 To 200
>
> icolor = 3
>
>
> Case Else
>
> 'Whatever
>
> End Select
>
> Target.Interior.ColorIndex = icolor
>
> End If
>
> What I would now like to do is replace the 'Case 0 to 90' (for example),
> with a reference to five pairs of cells (D2:E2, D3:E3, etc), so that if the
> value falls between those in either of these five pairs that the cell will be
> formatted accordingly.
>
> As mentioned in my original post, this would mean that as the target ranges
> for each pair change annually it will only require me to change the values in
> these five pairs of cells to effect changes to the conditional formatting,
> rather than to wade through acres of VBA code.
>
> Hope this helps and that in turn someone can help me.
>
> Many thanks,
>
> Richard
>
>
> "Dickie Worton" wrote:
>
> > Nigel,
> > Thanks for the tip, I'll document what I have and post it.
> > Regards,
> > Dickie
> >
> > "Nigel" wrote:
> >
> > > Hi Richard
> > > Rather than try and re-create the code you have already, post it here and
> > > someone can then amend to suit your needs
> > >
> > > --
> > > Cheers
> > > Nigel
> > >
> > >
> > >
> > > "Dickie Worton" <(E-Mail Removed)> wrote in message
> > > news3DE1336-32E3-4B3D-8342-(E-Mail Removed)...
> > > > Hi,
> > > >
> > > > I might be asking for too much here, but what I am trying to do is to set
> > > > up
> > > > a spreadsheet which contains conditional formatting for more than 3
> > > > conditions (not too hard). However, I also want to have each condition
> > > > reference pairs of cells elsewhere in the worksheet that will define the
> > > > two
> > > > parameters between which the input value will need to fall in order to
> > > > satisfy the condition criteria (does that make sense?)
> > > >
> > > > I have just about got to grips with the conditional formatting for more
> > > > than
> > > > 3 conditions, however it is referencing these cells in the 'Case' part of
> > > > the
> > > > VBA code that has me beaten. The reason that I don't want to simply put
> > > > the
> > > > parameters into the VBA code is that they are subject to change on an
> > > > annual
> > > > basis. As such, it would be preferable to simply update the pairs of
> > > > values
> > > > the sheet that the condition makes reference to, in order to avoid having
> > > > to
> > > > amend vast numbers of conditions in the VBA code on an annual basis.
> > > >
> > > > I gather that Excel 2007 no longer has the restriction of 3 conditional
> > > > formats, however the organisation where I work are unlikely to be moving
> > > > to
> > > > this anytime soon, hence my desire to find an answer to this problem. It
> > > > should also mean that anyone could update the sheet annually for parameter
> > > > changes, rather than needing to have an aptitude for Excel programming to
> > > > achieve this.
> > > >
> > > > I'm hoping rather than expecting, but if anyone has any ideas, they will
> > > > be
> > > > most gratefully received.
> > > >
> > > > Thanks,
> > > >
> > > > Richard
> > >
> > >
> > >

Hi Richard
Looks like Tom has given you the low down - I have nothing to add, other
than if you stored the values to check on another worksheet then change the
Range definitions by adding the sheet reference.

e.g

Case Sheets("Sheet2").Range("D2").Value To
Sheets("Sheet2").Range("E2").Value

I hope it works for you now.

--
Cheers
Nigel



"Dickie Worton" <(E-Mail Removed)> wrote in message
news:31DA2188-4DA7-4B26-AEF6-(E-Mail Removed)...
> OK Nigel (& anyone else!)
>
> Here is the code I have so far:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>
>
> Dim icolor As Integer
>
> If Not Intersect(Target, Range("D8:E32")) Is Nothing Then
>
> Select Case Target
>
> Case 0 To 90
>
> icolor = 10
>
> Case 90 To 100
>
> icolor = 43
>
> Case 100 To 110
>
> icolor = 6
>
> Case 110 To 120
>
> icolor = 44
>
> Case 120 To 200
>
> icolor = 3
>
>
> Case Else
>
> 'Whatever
>
> End Select
>
> Target.Interior.ColorIndex = icolor
>
> End If
>
> What I would now like to do is replace the 'Case 0 to 90' (for example),
> with a reference to five pairs of cells (D2:E2, D3:E3, etc), so that if
> the
> value falls between those in either of these five pairs that the cell will
> be
> formatted accordingly.
>
> As mentioned in my original post, this would mean that as the target
> ranges
> for each pair change annually it will only require me to change the values
> in
> these five pairs of cells to effect changes to the conditional formatting,
> rather than to wade through acres of VBA code.
>
> Hope this helps and that in turn someone can help me.
>
> Many thanks,
>
> Richard
>
>
> "Dickie Worton" wrote:
>
>> Nigel,
>> Thanks for the tip, I'll document what I have and post it.
>> Regards,
>> Dickie
>>
>> "Nigel" wrote:
>>
>> > Hi Richard
>> > Rather than try and re-create the code you have already, post it here
>> > and
>> > someone can then amend to suit your needs
>> >
>> > --
>> > Cheers
>> > Nigel
>> >
>> >
>> >
>> > "Dickie Worton" <(E-Mail Removed)> wrote in
>> > message
>> > news3DE1336-32E3-4B3D-8342-(E-Mail Removed)...
>> > > Hi,
>> > >
>> > > I might be asking for too much here, but what I am trying to do is to
>> > > set
>> > > up
>> > > a spreadsheet which contains conditional formatting for more than 3
>> > > conditions (not too hard). However, I also want to have each
>> > > condition
>> > > reference pairs of cells elsewhere in the worksheet that will define
>> > > the
>> > > two
>> > > parameters between which the input value will need to fall in order
>> > > to
>> > > satisfy the condition criteria (does that make sense?)
>> > >
>> > > I have just about got to grips with the conditional formatting for
>> > > more
>> > > than
>> > > 3 conditions, however it is referencing these cells in the 'Case'
>> > > part of
>> > > the
>> > > VBA code that has me beaten. The reason that I don't want to simply
>> > > put
>> > > the
>> > > parameters into the VBA code is that they are subject to change on an
>> > > annual
>> > > basis. As such, it would be preferable to simply update the pairs of
>> > > values
>> > > the sheet that the condition makes reference to, in order to avoid
>> > > having
>> > > to
>> > > amend vast numbers of conditions in the VBA code on an annual basis.
>> > >
>> > > I gather that Excel 2007 no longer has the restriction of 3
>> > > conditional
>> > > formats, however the organisation where I work are unlikely to be
>> > > moving
>> > > to
>> > > this anytime soon, hence my desire to find an answer to this problem.
>> > > It
>> > > should also mean that anyone could update the sheet annually for
>> > > parameter
>> > > changes, rather than needing to have an aptitude for Excel
>> > > programming to
>> > > achieve this.
>> > >
>> > > I'm hoping rather than expecting, but if anyone has any ideas, they
>> > > will
>> > > be
>> > > most gratefully received.
>> > >
>> > > Thanks,
>> > >
>> > > Richard
>> >
>> >
>> >

Nigel / Tom,
Many thanks to you both for your help with this, it will save me a massive
amount of time with the information I produce and I will doubtless find other
uses for this now that I know it can be done.
Kindest Regards,
Dickie

"Nigel" wrote:

> Hi Richard
> Looks like Tom has given you the low down - I have nothing to add, other
> than if you stored the values to check on another worksheet then change the
> Range definitions by adding the sheet reference.
>
> e.g
>
> Case Sheets("Sheet2").Range("D2").Value To
> Sheets("Sheet2").Range("E2").Value
>
> I hope it works for you now.
>
> --
> Cheers
> Nigel
>
>
>
> "Dickie Worton" <(E-Mail Removed)> wrote in message
> news:31DA2188-4DA7-4B26-AEF6-(E-Mail Removed)...
> > OK Nigel (& anyone else!)
> >
> > Here is the code I have so far:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >
> >
> > Dim icolor As Integer
> >
> > If Not Intersect(Target, Range("D8:E32")) Is Nothing Then
> >
> > Select Case Target
> >
> > Case 0 To 90
> >
> > icolor = 10
> >
> > Case 90 To 100
> >
> > icolor = 43
> >
> > Case 100 To 110
> >
> > icolor = 6
> >
> > Case 110 To 120
> >
> > icolor = 44
> >
> > Case 120 To 200
> >
> > icolor = 3
> >
> >
> > Case Else
> >
> > 'Whatever
> >
> > End Select
> >
> > Target.Interior.ColorIndex = icolor
> >
> > End If
> >
> > What I would now like to do is replace the 'Case 0 to 90' (for example),
> > with a reference to five pairs of cells (D2:E2, D3:E3, etc), so that if
> > the
> > value falls between those in either of these five pairs that the cell will
> > be
> > formatted accordingly.
> >
> > As mentioned in my original post, this would mean that as the target
> > ranges
> > for each pair change annually it will only require me to change the values
> > in
> > these five pairs of cells to effect changes to the conditional formatting,
> > rather than to wade through acres of VBA code.
> >
> > Hope this helps and that in turn someone can help me.
> >
> > Many thanks,
> >
> > Richard
> >
> >
> > "Dickie Worton" wrote:
> >
> >> Nigel,
> >> Thanks for the tip, I'll document what I have and post it.
> >> Regards,
> >> Dickie
> >>
> >> "Nigel" wrote:
> >>
> >> > Hi Richard
> >> > Rather than try and re-create the code you have already, post it here
> >> > and
> >> > someone can then amend to suit your needs
> >> >
> >> > --
> >> > Cheers
> >> > Nigel
> >> >
> >> >
> >> >
> >> > "Dickie Worton" <(E-Mail Removed)> wrote in
> >> > message
> >> > news3DE1336-32E3-4B3D-8342-(E-Mail Removed)...
> >> > > Hi,
> >> > >
> >> > > I might be asking for too much here, but what I am trying to do is to
> >> > > set
> >> > > up
> >> > > a spreadsheet which contains conditional formatting for more than 3
> >> > > conditions (not too hard). However, I also want to have each
> >> > > condition
> >> > > reference pairs of cells elsewhere in the worksheet that will define
> >> > > the
> >> > > two
> >> > > parameters between which the input value will need to fall in order
> >> > > to
> >> > > satisfy the condition criteria (does that make sense?)
> >> > >
> >> > > I have just about got to grips with the conditional formatting for
> >> > > more
> >> > > than
> >> > > 3 conditions, however it is referencing these cells in the 'Case'
> >> > > part of
> >> > > the
> >> > > VBA code that has me beaten. The reason that I don't want to simply
> >> > > put
> >> > > the
> >> > > parameters into the VBA code is that they are subject to change on an
> >> > > annual
> >> > > basis. As such, it would be preferable to simply update the pairs of
> >> > > values
> >> > > the sheet that the condition makes reference to, in order to avoid
> >> > > having
> >> > > to
> >> > > amend vast numbers of conditions in the VBA code on an annual basis.
> >> > >
> >> > > I gather that Excel 2007 no longer has the restriction of 3
> >> > > conditional
> >> > > formats, however the organisation where I work are unlikely to be
> >> > > moving
> >> > > to
> >> > > this anytime soon, hence my desire to find an answer to this problem.
> >> > > It
> >> > > should also mean that anyone could update the sheet annually for
> >> > > parameter
> >> > > changes, rather than needing to have an aptitude for Excel
> >> > > programming to
> >> > > achieve this.
> >> > >
> >> > > I'm hoping rather than expecting, but if anyone has any ideas, they
> >> > > will
> >> > > be
> >> > > most gratefully received.
> >> > >
> >> > > Thanks,
> >> > >
> >> > > Richard
> >> >
> >> >
> >> >
>
>
>

Following on from the terrific help I had from Tom & Nigel re the above, I
wondered if it was possible to take this one step further?

Is is possible for the code to not only reference values in a pair of cells
(as per the code below), but also to have the colour defined as being the
same colour as those cells where the value was found?

As there isn't too much knowledge of VBA coding within the team in which I
work (basically, I am the only one who knows anything about it), I want to
try and establish a sheet whereby once the VBA code is set up it won't need
to be accessed again. As such, if by defining the pairs of value parameters
and the cell colours in the worksheet it is possible to get these to be the
references for the VBA code then I would be extremely pleased and grateful,
to say the least.

Is this possible? Have I asked one question too many? Any help that is
offered would be most welcome.

Thanks,

Dickie


"Tom Ogilvy" wrote:

> If Not Intersect(Target, Range("D8:E32")) Is Nothing Then
>
>
> Select Case Target
>
> Case Range("D2").Value To Range("E2").Value
>
> icolor = 10
>
> Case Range("D3").Value To Range("E3").Value
>
> icolor = 43
>
> Case Range("D4").Value To Range("E4").Value
>
> icolor = 6
>
> Case Range("D5").Value To Range("E5").Value
>
> icolor = 44
>
> Case Range("D6").Value To Range("E6").Value
>
> icolor = 3
>
>
> Case Else
>
> 'Whatever
>
> End Select
>
> Target.Interior.ColorIndex = icolor
>
> End If
>
> --
> Regards,
> Tom Ogilvy
>
> "Dickie Worton" wrote:
>
> > OK Nigel (& anyone else!)
> >
> > Here is the code I have so far:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >
> >
> > Dim icolor As Integer
> >
> > If Not Intersect(Target, Range("D8:E32")) Is Nothing Then
> >
> > Select Case Target
> >
> > Case 0 To 90
> >
> > icolor = 10
> >
> > Case 90 To 100
> >
> > icolor = 43
> >
> > Case 100 To 110
> >
> > icolor = 6
> >
> > Case 110 To 120
> >
> > icolor = 44
> >
> > Case 120 To 200
> >
> > icolor = 3
> >
> >
> > Case Else
> >
> > 'Whatever
> >
> > End Select
> >
> > Target.Interior.ColorIndex = icolor
> >
> > End If
> >
> > What I would now like to do is replace the 'Case 0 to 90' (for example),
> > with a reference to five pairs of cells (D2:E2, D3:E3, etc), so that if the
> > value falls between those in either of these five pairs that the cell will be
> > formatted accordingly.
> >
> > As mentioned in my original post, this would mean that as the target ranges
> > for each pair change annually it will only require me to change the values in
> > these five pairs of cells to effect changes to the conditional formatting,
> > rather than to wade through acres of VBA code.
> >
> > Hope this helps and that in turn someone can help me.
> >
> > Many thanks,
> >
> > Richard
> >
> >
> > "Dickie Worton" wrote:
> >
> > > Nigel,
> > > Thanks for the tip, I'll document what I have and post it.
> > > Regards,
> > > Dickie
> > >
> > > "Nigel" wrote:
> > >
> > > > Hi Richard
> > > > Rather than try and re-create the code you have already, post it here and
> > > > someone can then amend to suit your needs
> > > >
> > > > --
> > > > Cheers
> > > > Nigel
> > > >
> > > >
> > > >
> > > > "Dickie Worton" <(E-Mail Removed)> wrote in message
> > > > news3DE1336-32E3-4B3D-8342-(E-Mail Removed)...
> > > > > Hi,
> > > > >
> > > > > I might be asking for too much here, but what I am trying to do is to set
> > > > > up
> > > > > a spreadsheet which contains conditional formatting for more than 3
> > > > > conditions (not too hard). However, I also want to have each condition
> > > > > reference pairs of cells elsewhere in the worksheet that will define the
> > > > > two
> > > > > parameters between which the input value will need to fall in order to
> > > > > satisfy the condition criteria (does that make sense?)
> > > > >
> > > > > I have just about got to grips with the conditional formatting for more
> > > > > than
> > > > > 3 conditions, however it is referencing these cells in the 'Case' part of
> > > > > the
> > > > > VBA code that has me beaten. The reason that I don't want to simply put
> > > > > the
> > > > > parameters into the VBA code is that they are subject to change on an
> > > > > annual
> > > > > basis. As such, it would be preferable to simply update the pairs of
> > > > > values
> > > > > the sheet that the condition makes reference to, in order to avoid having
> > > > > to
> > > > > amend vast numbers of conditions in the VBA code on an annual basis.
> > > > >
> > > > > I gather that Excel 2007 no longer has the restriction of 3 conditional
> > > > > formats, however the organisation where I work are unlikely to be moving
> > > > > to
> > > > > this anytime soon, hence my desire to find an answer to this problem. It
> > > > > should also mean that anyone could update the sheet annually for parameter
> > > > > changes, rather than needing to have an aptitude for Excel programming to
> > > > > achieve this.
> > > > >
> > > > > I'm hoping rather than expecting, but if anyone has any ideas, they will
> > > > > be
> > > > > most gratefully received.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Richard
> > > >
> > > >
> > > >