Max

Thanks again for your help. I have no problem getting the 1st part to work
but the VB does not work for me. At best I got it to change to one colour
(yellow) but it would not change to any other colour.

The cells which I need to colour have a $ value input.

Example

U2 = $150
U3 = $300
U3 = $0
U4 = $49

etc etc

These are the cells I need to change colour. (A different colour represents
a different person)


The cells which contain your folrmula are

IM2 =IF(ISNA(MATCH(TRUE,IN2:IU2,0)),"",MATCH(TRUE,IN2: IU2,0))
IM3 =IF(ISNA(MATCH(TRUE,IN3:IU3,0)),"",MATCH(TRUE,IN3: IU3,0))
IM4 =IF(ISNA(MATCH(TRUE,IN4:IU4,0)),"",MATCH(TRUE,IN4: IU4,0))

etc etc (This works OK)

So, essentially I need something that looks at your forlmula (in cells IM)
and changes the colours in column "U" based on the return from your formula.

I hope this helps.

Thanks




"Max" wrote:

With cell A2 housing the earlier formula returning the numbers 1 - 8, think
you could try tinkering with the sheet sub below to fill-color A2, with the
font color for A2 set to match the fill color so as to mask the underlying
number returned by the formula

To install:
Copy the sub
Right-click on the sheet tab View Code
Paste the sub into the code window on the right
Press Alt + Q to get back to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, fcolor As Integer
Set Target = [A2]
Select Case Target
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 Target
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With

End Sub

--
Max
Singapore
---
"John Calder" wrote:
Max

The person I am doing this for is really keen to have colour as the
identifier but if all else fails I will definately look at your option