Max

Once again thanks for your help. Your code does indeed work but not how I
intended it. The problem is that the cells I need coloured (U2:U51) also have
a manual input in each one. You solution as I understand it requires a
formula to be placed in each cell. Therfore I would not be able to place the
manual input that is required in each cell. The colour of each cell is not
dependant on the input of each cell it is dependant on the number that is
returned from your intial formula.

So, I have 8 tick boxes in cells C:J
Depending on which tick box I choose it returns a "TRUE" somewhere in the
range IN:IU
Your formula (in cell IM) returns the number in which the "TRUE" is returned.
What I need is that the corresponding cell in column U returns a colour
which represents the number returned by your formula. I can then place an
input (ie $300) into this cell.

eg:

1 = Yellow
2 = Red
3 = Pink
4 = Light Green
5 = Light Orange:
6 = Grey
7 = Light Blue
8 = Green

I hope I have managed to explain my situation a little better for you.

Thanks




"Max" wrote:

John, adapt the sub below (courtesy of a reply by MVP Jeff in another forum)
to suit the range to be colored, depending on the numbers returned by the
formulas in that range. As-is, it presumes that the range you want colored is
IM2:IM100, which contains formulas returning the numbers 1-8. I've tested it
and it works fine

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor, fcolor As Integer
Dim rng As Range
For Each rng In Range("IM2:IM100")
Select Case rng.Value
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select
With rng
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With
Next
End Sub

--
Max
Singapore
---