CF help

[serge]

I'm looking for a Conditional Formatting that,when 2 same number follow each other to be in one color.

then in a different color for 3 numbers for 4 and 5.

Excel Workbook
	A
1	1
2	5
3	5
4	6
5	3
6	3
7	3
8	4
9	8
10	1
11	1
12	1
13	1
14	5
15	2
16	9
17	9
18	9
19	9
20	9
Sheet1

Than you.

 

[Peter_SSs]

Cannot see a very easy way to do that but see if this is any use.

B1 houses a 0
B2 formula is copied down. (Column B could then be hidden if you want)
Select A2:Axx and apply the CF shown (adjust the $30 in the CF formulas to something below your last likely data)

Excel Workbook
	A	B
1		0
2	1	1
3	5
4	5	2
5	6	1
6	3
7	3
8	3	3
9	4	1
10	8	1
11	1
12	1
13	1
14	1	4
15	5	1
16	2	1
17	9
18	9
19	9
20	9
21	9	5
22
CF Groups

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =INDEX(B2:B$30,MATCH(TRUE,INDEX(ISNUMBER(B2:B$30),0),0))=5	Abc
A2	2. / Formula is =INDEX(B2:B$30,MATCH(TRUE,INDEX(ISNUMBER(B2:B$30),0),0))=4	Abc
A2	3. / Formula is =INDEX(B2:B$30,MATCH(TRUE,INDEX(ISNUMBER(B2:B$30),0),0))=3	Abc
A2	4. / Formula is =INDEX(B2:B$30,MATCH(TRUE,INDEX(ISNUMBER(B2:B$30),0),0))=2	Abc

 

[serge]

it works.

Thank you Peter.

 

[serge]

Hi Peter,

I'm having a problem with adapting your formulas to my actual data.

In the example above, I gave you the numbers starting in A1 ( as an example ) and your formulas works great, but after, I tried to adapted them to my actual data that start in cell : K6 and then from there the formulas where not giving me the right answer, I tried many ways to fix it but no success !!!.

My apology, I should have giving you the right data in the first place.
I hope you still can help me ?

Thank you.

 

[serge]

Hi Peter,

The formula need to be change a bit to work for the cell starting in K6, I tried to insert 10 columns and then 5 rows thinking the formula would adapted but it didn't !!!.

I hope you could see this reply ?

Thank you
Serge.

 

[Peter_SSs]

Excel Workbook
	K	L
5		0
6	1	1
7	5
8	5	2
9	6	1
10	3
11	3
12	3	3
13	4	1
14	8	1
15	1
16	1
17	1
18	1	4
19	5	1
20	2	1
21	9
22	9
23	9
24	9
25	9	5
CF Groups

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K6	1. / Formula is =INDEX(L6:L$40,MATCH(TRUE,INDEX(ISNUMBER(L6:L$40),0),0))=5	Abc
K6	2. / Formula is =INDEX(L6:L$40,MATCH(TRUE,INDEX(ISNUMBER(L6:L$40),0),0))=4	Abc
K6	3. / Formula is =INDEX(L6:L$40,MATCH(TRUE,INDEX(ISNUMBER(L6:L$40),0),0))=3	Abc
K6	4. / Formula is =INDEX(L6:L$40,MATCH(TRUE,INDEX(ISNUMBER(L6:L$40),0),0))=2	Abc

 

[serge]

You solve my problem, thank you very much Peter.