Conditional Formatting Based on Cell Values for more than 3 condit

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Nigel]

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.

 

[Guest]

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

 

[Guest]

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