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" wrote:
> 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" wrote:
>
> > 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
|
Similar Threads
