Conditional Formatting > 3 conditions & referencing cell values

[Dickie Worton]

Hello again,
I have previously posted (& received some terrific help) on a problem I have
been trying to solve concerning the use of more than 3 conditional formats.
To summarise, I now have a worksheet where, when I enter a value into a cell
in a range (D13:E37), the VBA code in the worksheet references a range of
other cells (which are all in pairs) and checks which pair of values the
value I have input falls between. The VBA code then colours cell dependent on
what colour has been specified. I have included a sample of the code below:

Private Sub Worksheet_Change(ByVal Target As Range)



Dim icolor As Integer

If Not Intersect(Target, Range("D13:E37")) Is Nothing Then


Select Case Target

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

icolor = 3

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

icolor = 44

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

icolor = 6

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

icolor = 43

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

icolor = 10

Case Range("D7").Value To Range("E7").Value

icolor = 43

Case Range("D8").Value To Range("E8").Value

icolor = 6

Case Range("D9").Value To Range("E9").Value

icolor = 44

Case Range("D10").Value To Range("E10").Value

icolor = 3

Case Else


However, if it is at all possible, what I would like to do is not to have to
edit the VBA code to specify the colour, i.e. simply have the cell coloured
in the same colour as the same pair of cells containing the parameters
between which the entered value falls.

For example (and using the code from above), if the value input falls
between the values in cell D9 & E9, rather than have to specify (icolor = 44)
in the VBA code, I would like to be able to simply have the cell detect and
use the same colour as is used in cells D9 & E9.

Basically, I am trying to countenance for a time when I might not be around
to make the changes to the VBA code. As such, if other users could simply
colour the pairs of cells in the worksheet and control what colours are used
in the code by this means it would be fantastic. The worksheet is used for
target setting and as our targets change each year it does not follow that a
pair of values will always equate to shading a cell red, orange, etc.

Does this make sense? I am happy to try and explain further if need be.
Furthermore, does anyone think that what I am after is achievable?

Any suggestions or help will be gratefully received.

Many thanks,

Dickie

 

[Mike H]

Dickie,

You can't refer to 2 cells to pick up the colour in case they are different
so I've referred to the colour in Column D.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("D13:E37")) Is Nothing Then

Select Case Target
Case Range("D2").Value To Range("E2").Value
icolor = Cells(2, 4).Interior.ColorIndex
Case Range("D3").Value To Range("E3").Value
icolor = Cells(3, 4).Interior.ColorIndex
Case Range("D4").Value To Range("E4").Value
icolor = Cells(4, 4).Interior.ColorIndex
Case Range("D5").Value To Range("E5").Value
icolor = Cells(5, 4).Interior.ColorIndex
Case Range("D6").Value To Range("E6").Value
icolor = Cells(6, 4).Interior.ColorIndex
Case Range("D7").Value To Range("E7").Value
icolor = Cells(7, 4).Interior.ColorIndex
Case Range("D8").Value To Range("E8").Value
icolor = Cells(8, 4).Interior.ColorIndex
Case Range("D9").Value To Range("E9").Value
icolor = Cells(9, 4).Interior.ColorIndex
Case Range("D10").Value To Range("E10").Value
icolor = Cells(10, 4).Interior.ColorIndex
Case Else
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub


Mike

 

[Dickie Worton]

Mike,

Just tried out what you supplied very quickly and it looks to be just what I
wanted.
As such, all I can say is....

Marvellous!

Thank you so, so much, you can't underestimate just how much time this will
save me and my colleagues.

Very best regards,

Dickie

 

[Mike H]

Your welcome and thanks for the feedback

Mike,

Just tried out what you supplied very quickly and it looks to be just what I
wanted.
As such, all I can say is....

Marvellous!

Thank you so, so much, you can't underestimate just how much time this will
save me and my colleagues.

Very best regards,

Dickie