Is it possible to apply conditional format always for last row numbers?

[motilulla]

Hello,

I am looking does it possible to apply conditional format always for last row numbers?

For example in the row 5 numbers apply conditional format, which may highlight only last row numbers (in this example last row is 18) but it keep changing

			CF	12	27	32	75	86	90	98	100	107	124	137	138	139	140	145	152	157	210	242	249
				27	75	98	100	107	137	138	139	140	152	249
				27	75	98	138	140
				98	138	140
				98	138	140
				138	140
				75	86	90	98	100	107	124	137	138	139	140	145	152	157	210	242	249
				75	86	90	100	107	137	138	139	140	145	152	157	210	242	249
				86	90	107	137	139	152	210
				12	27	32	75	86	90	98	100	107	124	137	138	139	140	145	157	210	242
				12	27	32	75	86	90	98	100	138	145	210	242

For example the sample image is attached.

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Peter_SSs]

Are you trying to format
- row 5 if the number appear in the last row, or
- the last row if the number appears in row 5, or
- both of the above, or
- something else?

 

[motilulla]

Are you trying to format
- row 5 if the number appear in the last row, or
- the last row if the number appears in row 5, or
- both of the above, or
- something else?

Hello Peter_SSs, thank you for the reply

I need CF to Hifghlight - row 5 if the number appears in the last row.

Regards,
Moti

 

[Peter_SSs]

I need CF to Hifghlight - row 5 if the number appears in the last row.

Thanks for the clarification.
I would use one helper cell (I used C5) to reduce the calculation burden & simplify the CF formulas.
I have assumed that your data will not go past row 1000 but you can easily increase or decrease that number.

21 05 11.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
5	13	CF	12	27	32	75	86	90	98	100	107	124	137	138	139	140	145	152	157	210	242	249
6
7			27	75	98	100	107	137	138	139	140	152	249
8			27	75	98	138	140
9			98	138	140
10			98	138	140
11			138	140
12
13			75	86	90	98	100	107	124	137	138	139	140	145	152	157	210	242	249
14			75	86	90	100	107	137	138	139	140	145	152	157	210	242	249
15			86	90	107	137	139	152	210
16
17			12	27	32	75	86	90	98	100	107	124	137	138	139	140	145	157	210	242
18			12	27	32	75	86	90	98	100	138	145	210	242
19
CF LastRow

Cell Formulas
Range	Formula
C5	C5	=LOOKUP(9.99E+307,E6:E1000,ROW(E6:E1000))-ROW(E5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:X5	Expression	=MATCH(E5,INDEX($E$6:$X$1000,$C$5,0),0)	text	NO

 

[motilulla]

Thanks for the clarification.
I would use one helper cell (I used C5) to reduce the calculation burden & simplify the CF formulas.
I have assumed that your data will not go past row 1000 but you can easily increase or decrease that number.

21 05 11.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
5	13	CF	12	27	32	75	86	90	98	100	107	124	137	138	139	140	145	152	157	210	242	249
6
7			27	75	98	100	107	137	138	139	140	152	249
8			27	75	98	138	140
9			98	138	140
10			98	138	140
11			138	140
12
13			75	86	90	98	100	107	124	137	138	139	140	145	152	157	210	242	249
14			75	86	90	100	107	137	138	139	140	145	152	157	210	242	249
15			86	90	107	137	139	152	210
16
17			12	27	32	75	86	90	98	100	107	124	137	138	139	140	145	157	210	242
18			12	27	32	75	86	90	98	100	138	145	210	242
19
CF LastRow

Cell Formulas
Range	Formula
C5	C5	=LOOKUP(9.99E+307,E6:E1000,ROW(E6:E1000))-ROW(E5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:X5	Expression	=MATCH(E5,INDEX($E$6:$X$1000,$C$5,0),0)	text	NO

Wow! Peter_SSs, it is an amazing! Solution. My end row could be in the feature 4000+, i just increased the number 1000 to 4108 and it is working, as I wanted.

I am grateful to you for your kind help and time you spent to solve my request.

Have a good day and a great week ahead

Regards,
Moti

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, I forgot to mention that my solution assumes that every row of numbers begins in column E (like your samples).

 

[motilulla]

You're welcome. Thanks for the follow-up.

BTW, I forgot to mention that my solution assumes that every row of numbers begins in column E (like your samples).

Yes, I noticed that is an amazing part as soon as the any new number is entered in column E, immediately in the row 5 CF is replaced as per last line-entered new number, which is superb!!!

Thank you Peter, for giving a pretty good Conditional Format solution. ?

Kind Regards,
Moti