Excel 2003 Conditional Formatting

[adulted]

Hello All,

Working in Excel 2003

This is hard to explain, however I will try with this example, which changes every time I update the sheet and/or add or modify formulas.

In the cell reanges L6 : Q15, I have 3 conditions set.
The 1st two are working, however my 3rd condition I set to turn duplicated Back fill.

In this example my black cells are P14 and Q14 and N15 and O15. Which seems to work fine.

I can see they are duplicates.

However cells P10, Q10 contain The number 39, also cells M14, N14 contain the number 35

They didn't turn black howerver I can see they are duplicates.

Here are the three conditions in order
1. Formula Is =MATCH(L6,$L$2:$Q$2,0)
2. Cell Value Is Equal To =$R$2, formatted with color Pink
3. Formula Is =COUNTIF($L6:Q6,L6)>1, formatted with colour Black

Excel 2003
	L	M	N	O	P	Q
6	2	8	24	30	40	45
7	5	22	27	39	40	41
8	10	16	23	33	35	41
9	3	7	23	35	37	45
10	1	4	21	34	39	39
11	14	22	28	34	35	37
12	14	15	17	26	35	42
13	10	29	32	37	46	49
14	23	35	35	36	48	48
15	18	26	32	32	34	48
Lotto

A little more are of my spreadsheet, maby it will help what is happening.

Excel 2003
	I	J	K	L	M	N	O	P	Q	R
1	0			Called Numbers
2	1	Sat Nov 21 2015		2	4	20	35	39	42	28
3	0									Wins
4	0			Match 3 !!!
5	0
6		1		2	8	24	30	40	45	1
7		1		5	22	27	39	40	41	1
8		1		10	16	23	33	35	41	1
9		1		3	7	23	35	37	45	1
10	Win !!!	3		1	4	21	34	39	39	3
11		1		14	22	28	34	35	37	1
12		2		14	15	17	26	35	42	2
13		0		10	29	32	37	46	49	0
14		2		23	35	35	36	48	48	2
15		0		18	26	32	32	34	48	0
Lotto

Cell Formulas
Range	Formula
J2	=NOW()
J6	=(R6)
J7	=(R7)
J8	=(R8)
J9	=(R9)
J10	=(R10)
J11	=(R11)
J12	=(R12)
J13	=(R13)
J14	=(R14)
J15	=(R15)
L3	=IF(I1>0,"Match 2 !!!","")
L4	=IF(I2>0,"Match 3 !!!","")
L5	=IF(I4>0,"Match 5 !!!","")
O4	=IF(I3>0,"Match 4 !!!","")
O5	=IF(I5>0,"Jackpot !!!","")
I1	=COUNTIF(U6:U32,"=7")
I2	=COUNTIF($J$6:$J$15,"=3")
I3	=COUNTIF($J$6:$J$15,"=4")
I4	=COUNTIF($J$6:$J$15,"=5")
I5	=COUNTIF($J$6:$J$15,"=6")
I6	=IF(U6>2,"Win !!!","")
I7	=IF(U7>2,"Win !!!","")
I8	=IF(U8>2,"Win !!!","")
I9	=IF(U9>2,"Win !!!","")
I10	=IF(U10>2,"Win !!!","")
I11	=IF(U11>2,"Win !!!","")
I12	=IF(U12>2,"Win !!!","")
I13	=IF(U13>2,"Win !!!","")
I14	=IF(U14>2,"Win !!!","")
I15	=IF(U15>2,"Win !!!","")
K6	=IF(OR($J6=5,$J6=2)*ISNUMBER(MATCH($R$2,$L6:$Q6,0)),"Bonus","")
K7	=IF(OR($J7=5,$J7=2)*ISNUMBER(MATCH($R$2,$L7:$Q7,0)),"Bonus","")
K8	=IF(OR($J8=5,$J8=2)*ISNUMBER(MATCH($R$2,$L8:$Q8,0)),"Bonus","")
K9	=IF(OR($J9=5,$J9=2)*ISNUMBER(MATCH($R$2,$L9:$Q9,0)),"Bonus","")
K10	=IF(OR($J10=5,$J10=2)*ISNUMBER(MATCH($R$2,$L10:$Q10,0)),"Bonus","")
K11	=IF(OR($J11=5,$J11=2)*ISNUMBER(MATCH($R$2,$L11:$Q11,0)),"Bonus","")
K12	=IF(OR($J12=5,$J12=2)*ISNUMBER(MATCH($R$2,$L12:$Q12,0)),"Bonus","")
K13	=IF(OR($J13=5,$J13=2)*ISNUMBER(MATCH($R$2,$L13:$Q13,0)),"Bonus","")
K14	=IF(OR($J14=5,$J14=2)*ISNUMBER(MATCH($R$2,$L14:$Q14,0)),"Bonus","")
K15	=IF(OR($J15=5,$J15=2)*ISNUMBER(MATCH($R$2,$L15:$Q15,0)),"Bonus","")
R6	=SUM(C6:H6)
R7	=SUM(C7:H7)
R8	=SUM(C8:H8)
R9	=SUM(C9:H9)
R10	=SUM(C10:H10)
R11	=SUM(C11:H11)
R12	=SUM(C12:H12)
R13	=SUM(C13:H13)
R14	=SUM(C14:H14)
R15	=SUM(C15:H15)

Thanks,
Paul

 

[konew1]

It is a long time since I used xl2003 but I suggest you look at this

maybe
3. Formula Is =COUNTIF($L6:$Q6,L6)>1, formatted with colour Black

 

[Peter_SSs]

1. I believe konew1 is correct in that you should have that extra $ sign in the condition 3 formula. =COUNTIF($L6:$Q6,L6)>1

2. However, your other issue is that Excel can only apply one conditional format to a cell. As soon as one of the conditions is met, the cell is formatted that way and no further checking is done. After all, how could a cell be pink and black anyway?

The reason those other duplicate cells (values 35 and 39) are not black is that those values appear in L2:Q2 so the first condition is met for them & no more checking is done.