Highlight from the bottom n'th number Cell

[motilulla]

Hello,</SPAN></SPAN>

I would like to highlight from the bottom n'th number Cell of the column, column range is variable (the n'th number is in the D2=8 is for column D & the n'th number is in the E2=12 is for column E</SPAN></SPAN>

Like this

Book1
	C	D	E	F
1		From Bottom	From Bottom
2		8	12
3
4		Numbers	Numbers
5		45	45
6		43	43
7		42	42
8		14	14
9		21	21
10		2	2
11		26	26
12		43	43
13		21	21
14		5	5
15		28	28
16		8	8
17		25	25
18		31	31
19		47	47
20		28	28
21		12	12
22		10	10
23		14	14
24		46	46
25		49	49
26		11	11
27		26	26
28		32	32
29		42	42
30		33	33
31		1	1
32		14	14
33		50	50
34		44	44
35		28	28
36		7	7
37		15	15
38		33	33
39		47	47
40		49	49
41		43	43
42		7	7
43		23	23
44		23	23
45		17	17
46		6	6
47		50	50
48		18	18
49		22	22
50		24	24
51		39	39
52		48	48
53		20	20
54		38	38
55		8	8
56		19	19
57		37	37
58
59
Sheet1

</SPAN></SPAN>
Thank you all</SPAN></SPAN>
Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[mikerickson]

How about conditional formatting with the formula =(MATCH(9E+99, D:D)-D$2-ROW()+1=0)

 

[motilulla]

How about conditional formatting with the formula =(MATCH(9E+99, D:D)-D$2-ROW()+1=0)

Hello mikerickson, Thank you it does work but as I need to apply with multiple columns and with 20000 over rows it slows down other codes.</SPAN></SPAN>

Could it be lighter solution?</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[mikerickson]

The 20,000 rows isn't much of a problem. (Actually its an advantage since there is a maximum number of rows the D:D can be changed to D$1:D$20000)

How many columns make up "multiple"?
I'm also curious why you are highlighting this row? If it to make it easy for the user to find, thats what CF is for, but if the nth cell is being used for downstream calculations, the highlight isn't the way to go for that.

 

[motilulla]

The 20,000 rows isn't much of a problem. (Actually its an advantage since there is a maximum number of rows the D:D can be changed to D$1:D$20000)

How many columns make up "multiple"?
I'm also curious why you are highlighting this row? If it to make it easy for the user to find, thats what CF is for, but if the nth cell is being used for downstream calculations, the highlight isn't the way to go for that.

Hello mikerickson, Thank you limiting the ranges that is the fine now it is much better, but come up with new issue, which I did realize now at the end after the "1" I do put F</SPAN></SPAN>

Please see the example below does this can be solved, so in the D can be highlight 1 not the 26 </SPAN></SPAN>

Book1
	C	D	E	F
1		From Bottom	From Bottom
2		2	2
3
4		Numbers	Numbers
5		45	45
6		43	43
7		42	42
8		14	14
9		21	21
10		2	2
11		26	26
12		43	43
13		21	21
14		5	5
15		28	28
16		8	8
17		25	25
18		31	31
19		47	47
20		28	28
21		12	12
22		10	10
23		14	14
24		46	46
25		49	49
26		11	11
27		26	26
28		32	32
29		42	42
30		33	33
31		1	1
32		F	F
33		50	50
34		44	44
35		28	28
36		7	7
37		15	15
38		33	33
39		47	47
40		49	49
41		43	43
42		7	7
43		23	23
44		23	23
45		17	17
46		6	6
47		50	50
48		18	18
49		22	22
50		24	24
51		39	39
52		48	48
53		20	20
54		38	38
55		26	8
56		1	19
57		F	37
58
59
Sheet1

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[mikerickson]

If the column is a mix of numbers and text, the MATCH approach is more complicated.

Instead of MATCH(9E+99,D:D), you'ld need to use IFERROR(MAX(MATCH(9E+99,D:D),MATCH("zzzz",D:D)),IFERROR(MATCH(9E+99,D:D),MATCH("zzzz",D:D)))

 

[motilulla]

If the column is a mix of numbers and text, the MATCH approach is more complicated.

Instead of MATCH(9E+99,D:D), you'ld need to use IFERROR(MAX(MATCH(9E+99,D:D),MATCH("zzzz",D:D)),IFERROR(MATCH(9E+99,D:D),MATCH("zzzz",D:D)))

Hello mikerickson, Thank you once again for building a formula to work with alphanumerical values, unfortunately it does not worked with my version, goggling </SPAN></SPAN>I found that (IFERROR has only existed in versions from 2007 onwards), if I apply the formula in any cell out side it results #¿NAME? Where the previous post#2 formula was resulting TRUE OR FALSE </SPAN></SPAN>

May it could be easy the VBA solution </SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Marcelo Branco]

Maybe...

Select, say, D5:E200

In Conditional Formatting use this formula
=COUNTA(D5:D$200)=D$2
pick the format you want

M.

 

[motilulla]

Maybe...

Select, say, D5:E200

In Conditional Formatting use this formula
=COUNTA(D5:D$200)=D$2
pick the format you want

M.

Beautiful!! Marcelo Branco, this one is a perfect formula, checked with original data has multiple row and columns worked nicely.</SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Have a pleasant day </SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>