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" wrote in message
...
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