Row conditional formatting by criteria min and max of the column "K" value

[motilulla]

Hello,

My data range C2:K32 i want to highlight 2 rows by finding min/max value of the column "K" and highlight entire row.
One formula to find Min value in the column ranges K2:K32 and highlights the entire row.
Second formula to find Max value in the column ranges K2:K32 and highlights the entire row.

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5			Seriol Num	Part Num	Year	Seriol	Row Num	Sum	Sum Rank	Delay	Delay Rank
6			1	26184	19/20	37	2.267	176	81	77	50
7			2	1905	18/19	51	2.212	72	77	132	61
8			3	25452	20/21	37	2.326	71	75	18	17
9			4	78688	19/20	34	2.264	99	80	80	51
10			5	12678	19/20	39	2.269	39	66	75	49
11			6	25777	17/18	42	2.136	27	50	208	69
12			7	72839	20/21	50	2.339	71	75	5	6
13			8	72427	17/18	10	2.104	38	65	240	72
14			9	19998	19/20	50	2.280	29	54	64	45
15			10	15418	20/21	54	2.343	79	78	1	2
16			11	33165	19/20	21	2.251	35	60	93	53
17			12	29449	20/21	22	2.311	41	70	33	30
18			13	31643	20/21	24	2.313	48	72	31	28
19			14	79859	20/21	46	2.335	26	47	9	10
20			15	11651	20/21	5	2.294	21	37	50	39
21			16	41969	20/21	40	2.329	35	60	15	14
22			17	94305	20/21	1	2.290	20	34	54	41
23			18	86136	19/20	58	2.288	24	43	56	42
24			19	59290	20/21	47	2.336	65	74	8	9
25			20	5118	18/19	19	2.180	26	47	164	64
26			21	82866	20/21	34	2.323	30	57	21	20
27			22	44082	20/21	49	2.338	35	60	6	7
28			23	42728	20/21	9	2.298	17	28	46	37
29			24	44191	04/05	41	1.433	15	21	911	81
30			25	11561	20/21	18	2.307	29	54	37	32
31			26	54217	20/21	26	2.315	19	32	29	27
32			27	78547	17/18	33	2.127	16	25	217	70
33
34

For example, the sample image is attached.

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Toadstool]

Hi Motilulla,

Does this do what you ask?

Motilulla.xlsx
	C	D	E	F	G	H	I	J	K
1	Seriol Num	Part Num	Year	Seriol	Row Num	Sum	Sum Rank	Delay	Delay Rank
2	1	26184	19/20	37	2.267	176	81	77	50
3	2	1905	18/19	51	2.212	72	77	132	61
9	8	72427	17/18	10	2.104	38	65	240	72
10	9	19998	19/20	50	2.28	29	54	64	45
11	10	15418	20/21	54	2.343	79	78	1	2
12	11	33165	19/20	21	2.251	35	60	93	53
23	22	44082	20/21	49	2.338	35	60	6	7
24	23	42728	20/21	9	2.298	17	28	46	37
25	24	44191	5-Apr	41	1.433	15	21	911	81
26	25	11561	20/21	18	2.307	29	54	37	32
27	26	54217	20/21	26	2.315	19	32	29	27
28	27	78547	17/18	33	2.127	16	25	217	70
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:K28	Expression	=$K2=MAX($K:$K)	text	NO
C2:K28	Expression	=$K2=MIN($K:$K)	text	NO

 

[Radoslaw Poprawski]

Here you go

Book1
	C	D	E	F	G	H	I	J	K	L	M
1	Seriol Num	Part Num	Year	Seriol	Row Num	Sum	Sum Rank	Delay	Delay Rank	True/False For Max	True/False for Min
2	1	26184	19/20	37	2.267	176	81	77	50	FALSE	FALSE
3	2	1905	18/19	51	2.212	72	77	132	61	FALSE	FALSE
4	3	25452	20/21	37	2.326	71	75	18	17	FALSE	FALSE
5	4	78688	19/20	34	2.264	99	80	80	51	FALSE	FALSE
6	5	12678	19/20	39	2.269	39	66	75	49	FALSE	FALSE
7	6	25777	17/18	42	2.136	27	50	208	69	FALSE	FALSE
8	7	72839	20/21	50	2.339	71	75	5	6	FALSE	FALSE
9	8	72427	17/18	10	2.104	38	65	240	72	FALSE	FALSE
10	9	19998	19/20	50	2.280	29	54	64	45	FALSE	FALSE
11	10	15418	20/21	54	2.343	79	78	1	2	FALSE	TRUE
12	11	33165	19/20	21	2.251	35	60	93	53	FALSE	FALSE
13	12	29449	20/21	22	2.311	41	70	33	30	FALSE	FALSE
14	13	31643	20/21	24	2.313	48	72	31	28	FALSE	FALSE
15	14	79859	20/21	46	2.335	26	47	9	10	FALSE	FALSE
16	15	11651	20/21	5	2.294	21	37	50	39	FALSE	FALSE
17	16	41969	20/21	40	2.329	35	60	15	14	FALSE	FALSE
18	17	94305	20/21	1	2.290	20	34	54	41	FALSE	FALSE
19	18	86136	19/20	58	2.288	24	43	56	42	FALSE	FALSE
20	19	59290	20/21	47	2.336	65	74	8	9	FALSE	FALSE
21	20	5118	18/19	19	2.180	26	47	164	64	FALSE	FALSE
22	21	82866	20/21	34	2.323	30	57	21	20	FALSE	FALSE
23	22	44082	20/21	49	2.338	35	60	6	7	FALSE	FALSE
24	23	42728	20/21	9	2.298	17	28	46	37	FALSE	FALSE
25	24	44191	04.maj	41	1.433	15	21	911	81	TRUE	FALSE
26	25	11561	20/21	18	2.307	29	54	37	32	FALSE	FALSE
27	26	54217	20/21	26	2.315	19	32	29	27	FALSE	FALSE
28	27	78547	17/18	33	2.127	16	25	217	70	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
L2:L28	L2	=MAX($K$2:$K$28)=$K2
M2:M28	M2	=MIN($K$2:$K$28)=$K2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:K28	Expression	=MIN($K$2:$K$28)=$K1	text	NO
C1:K28	Expression	=MAX($K$2:$K$28)=$K1	text	NO

Is that what you wished for?

 

[motilulla]

Hi Motilulla,

Does this do what you ask?

Motilulla.xlsx
	C	D	E	F	G	H	I	J	K
1	Seriol Num	Part Num	Year	Seriol	Row Num	Sum	Sum Rank	Delay	Delay Rank
2	1	26184	19/20	37	2.267	176	81	77	50
3	2	1905	18/19	51	2.212	72	77	132	61
9	8	72427	17/18	10	2.104	38	65	240	72
10	9	19998	19/20	50	2.28	29	54	64	45
11	10	15418	20/21	54	2.343	79	78	1	2
12	11	33165	19/20	21	2.251	35	60	93	53
23	22	44082	20/21	49	2.338	35	60	6	7
24	23	42728	20/21	9	2.298	17	28	46	37
25	24	44191	5-Apr	41	1.433	15	21	911	81
26	25	11561	20/21	18	2.307	29	54	37	32
27	26	54217	20/21	26	2.315	19	32	29	27
28	27	78547	17/18	33	2.127	16	25	217	70
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:K28	Expression	=$K2=MAX($K:$K)	text	NO
C2:K28	Expression	=$K2=MIN($K:$K)	text	NO

Toadstool, thank you for the formula I did modified a bit as per my requirements it seems it highlight first 2 parts ok and in the 3rd part it highlighted 5 rows may be formulas does not work with multi ranges for the same column Min and max values.

Here is how I applied the formulas for 3 different ranges

Range C2:K28 =$K2=MAX($K2:$K32)
Range C2:K28 =$K2=MIN($K2:$K32)

Range C29:K55 =$K29=MAX($K29:$K55)
Range C29:K55 =$K29=MIN($K29:$K55)

Range C56:K82 =$K56=MAX($K56:$K82)
Range C56:K82 =$K56=MIN($K56:$K82)

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Seriol Num	Part Num	Year	Seriol	Row Num	Sum	Sum Rank	Delay	Delay Rank
2			1	26184	19/20	37	2.267	176​	81	77	50
3			2	1905	18/19	51	2.212	72​	77	132	61
4			3	25452	20/21	37	2.326	71​	75	18	17
5			4	78688	19/20	34	2.264	99​	80	80	51
6			5	12678	19/20	39	2.269	39​	66	75	49
7			6	25777	17/18	42	2.136	27​	50	208	69
8			7	72839	20/21	50	2.339	71​	75	5	6
9			8	72427	17/18	10	2.104	38​	65	240	72
10			9	19998	19/20	50	2.280	29​	54	64	45
11			10	15418	20/21	54	2.343	79​	78	1	2
12			11	33165	19/20	21	2.251	35​	60	93	53
13			12	29449	20/21	22	2.311	41​	70	33	30
14			13	31643	20/21	24	2.313	48​	72	31	28
15			14	79859	20/21	46	2.335	26​	47	9	10
16			15	11651	20/21	5	2.294	21​	37	50	39
17			16	41969	20/21	40	2.329	35​	60	15	14
18			17	94305	20/21	1	2.290	20​	34	54	41
19			18	86136	19/20	58	2.288	24​	43	56	42
20			19	59290	20/21	47	2.336	65​	74	8	9
21			20	5118	18/19	19	2.180	26​	47	164	64
22			21	82866	20/21	34	2.323	30​	57	21	20
23			22	44082	20/21	49	2.338	35​	60	6	7
24			23	42728	20/21	9	2.298	17​	28	46	37
25			24	44191	04/05	41	1.433	15​	21	911	81
26			25	11561	20/21	18	2.307	29​	54	37	32
27			26	54217	20/21	26	2.315	19​	32	29	27
28			27	78547	17/18	33	2.127	16​	25	217	70
29			28	26184	20/21	51	2.340	82​	79	4	5
30			29	1905	19/20	43	2.273	26​	47	71	48
31			30	25452	18/19	44	2.205	40​	69	139	62
32			31	78688	18/19	36	2.197	42​	71	147	63
33			32	12678	20/21	38	2.327	21​	37	17	16
34			33	25777	20/21	29	2.318	22​	40	26	24
35			34	72839	19/20	57	2.287	34​	58	57	43
36			35	72427	19/20	16	2.246	16​	25	98	54
37			36	19998	20/21	11	2.300	20​	34	44	35
38			37	15418	19/20	3	2.233	39​	66	111	57
39			38	33165	20/21	48	2.337	21​	37	7	8
40			39	29449	19/20	6	2.236	25​	45	108	56
41			40	31643	18/19	63	2.224	25​	45	120	58
42			41	79859	11/12	20	1.760	10​	11	584	79
43			42	11651	14/15	35	1.936	9​	10	408	77
44			43	41969	16/17	28	2.055	13​	16	289	75
45			44	94305	17/18	47	2.141	11​	13	203	68
46			45	86136	15/16	41	1.998	5​	2	346	76
47			46	59290	20/21	55	2.344	37​	63	0	1
48			47	5118	18/19	58	2.219	13​	16	125	60
49			48	82866	19/20	22	2.252	15​	21	92	52
50			49	44082	20/21	31	2.320	15​	21	24	22
51			50	42728	20/21	39	2.328	6​	4	16	15
52			51	44191	07/08	22	1.540	3​	1	804	80
53			52	11561	20/21	36	2.325	24​	43	19	18
54			53	54217	16/17	57	2.084	7​	6	260	74
55			54	78547	20/21	17	2.306	11​	13	38	33
56			55	26184	20/21	41	2.330	63​	73	14	13
57			56	1905	20/21	28	2.317	28​	52	27	25
58			57	25452	20/21	42	2.331	39​	66	13	12
59			58	78688	20/21	45	2.334	37​	63	10	11
60			59	12678	16/17	59	2.086	10​	11	258	73
61			60	25777	19/20	47	2.277	22​	40	67	46
62			61	72839	20/21	33	2.322	27​	50	22	21
63			62	72427	18/19	10	2.171	22​	40	173	66
64			63	19998	20/21	27	2.316	17​	28	28	26
65			64	15418	19/20	13	2.243	34​	58	101	55
66			65	33165	20/21	10	2.299	19​	32	45	36
67			66	29449	19/20	45	2.275	16​	25	69	47
68			67	31643	20/21	20	2.309	28​	52	35	31
69			68	79859	19/20	56	2.286	15​	21	58	44
70			69	11651	20/21	23	2.312	7​	6	32	29
71			70	41969	20/21	53	2.342	20​	34	2	3
72			71	94305	17/18	23	2.117	7​	6	227	71
73			72	86136	17/18	55	2.149	7​	6	195	67
74			73	59290	20/21	16	2.305	29​	54	39	34
75			74	5118	20/21	30	2.319	14​	20	25	23
76			75	82866	18/19	62	2.223	17​	28	121	59
77			76	44082	20/21	8	2.297	13​	16	47	38
78			77	42728	20/21	52	2.341	13​	16	3	4
79			78	44191	12/13	9	1.805	5​	2	539	78
80			79	11561	20/21	4	2.293	11​	13	51	40
81			80	54217	18/19	17	2.178	6​	4	166	65
82			81	78547	20/21	35	2.324	18​	31	20	19
83
84

Result image is attached

Have a nice time

Kind regards,
Moti

 

[motilulla]

Here you go

Book1
	C	D	E	F	G	H	I	J	K	L	M
1	Seriol Num	Part Num	Year	Seriol	Row Num	Sum	Sum Rank	Delay	Delay Rank	True/False For Max	True/False for Min
2	1	26184	19/20	37	2.267	176	81	77	50	FALSE	FALSE
3	2	1905	18/19	51	2.212	72	77	132	61	FALSE	FALSE
4	3	25452	20/21	37	2.326	71	75	18	17	FALSE	FALSE
5	4	78688	19/20	34	2.264	99	80	80	51	FALSE	FALSE
6	5	12678	19/20	39	2.269	39	66	75	49	FALSE	FALSE
7	6	25777	17/18	42	2.136	27	50	208	69	FALSE	FALSE
8	7	72839	20/21	50	2.339	71	75	5	6	FALSE	FALSE
9	8	72427	17/18	10	2.104	38	65	240	72	FALSE	FALSE
10	9	19998	19/20	50	2.280	29	54	64	45	FALSE	FALSE
11	10	15418	20/21	54	2.343	79	78	1	2	FALSE	TRUE
12	11	33165	19/20	21	2.251	35	60	93	53	FALSE	FALSE
13	12	29449	20/21	22	2.311	41	70	33	30	FALSE	FALSE
14	13	31643	20/21	24	2.313	48	72	31	28	FALSE	FALSE
15	14	79859	20/21	46	2.335	26	47	9	10	FALSE	FALSE
16	15	11651	20/21	5	2.294	21	37	50	39	FALSE	FALSE
17	16	41969	20/21	40	2.329	35	60	15	14	FALSE	FALSE
18	17	94305	20/21	1	2.290	20	34	54	41	FALSE	FALSE
19	18	86136	19/20	58	2.288	24	43	56	42	FALSE	FALSE
20	19	59290	20/21	47	2.336	65	74	8	9	FALSE	FALSE
21	20	5118	18/19	19	2.180	26	47	164	64	FALSE	FALSE
22	21	82866	20/21	34	2.323	30	57	21	20	FALSE	FALSE
23	22	44082	20/21	49	2.338	35	60	6	7	FALSE	FALSE
24	23	42728	20/21	9	2.298	17	28	46	37	FALSE	FALSE
25	24	44191	04.maj	41	1.433	15	21	911	81	TRUE	FALSE
26	25	11561	20/21	18	2.307	29	54	37	32	FALSE	FALSE
27	26	54217	20/21	26	2.315	19	32	29	27	FALSE	FALSE
28	27	78547	17/18	33	2.127	16	25	217	70	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
L2:L28	L2	=MAX($K$2:$K$28)=$K2
M2:M28	M2	=MIN($K$2:$K$28)=$K2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:K28	Expression	=MIN($K$2:$K$28)=$K1	text	NO
C1:K28	Expression	=MAX($K$2:$K$28)=$K1	text	NO

Is that what you wished for?

Radoslaw Poprawski, CF formula worked perfect! I do appreciate your help

Have a nice time

Kind Regards,
Moti

 

[Radoslaw Poprawski]

@motilulla
you are welcome

 

[Peter_SSs]

Here is how I applied the formulas for 3 different ranges

Range C2:K28 =$K2=MAX($K2:$K32)
Range C2:K28 =$K2=MIN($K2:$K32)

Range C29:K55 =$K29=MAX($K29:$K55)
Range C29:K55 =$K29=MIN($K29:$K55)

Range C56:K82 =$K56=MAX($K56:$K82)
Range C56:K82 =$K56=MIN($K56:$K82)

You are missing $ signs on the row values
Range C2:K28 =$K2=MAX($K$2:$K$32)

Similar with all the other formulas.

 

[motilulla]

You are missing $ signs on the row values
Range C2:K28 =$K2=MAX($K$2:$K$32)

Similar with all the other formulas.

Peter_SSs, I am sorry for my mistake, thank you very much for the observation and correcting my error, yes true adding $ signs formula worked perfectly! ?

Have a nice time

Regards,
Moti

 

[motilulla]

Toadstool, thank you for the formula I did modified a bit as per my requirements it seems it highlight first 2 parts ok and in the 3rd part it highlighted 5 rows may be formulas does not work with multi ranges for the same column Min and max values.

Here is how I applied the formulas for 3 different ranges

Range C2:K28 =$K2=MAX($K2:$K32)
Range C2:K28 =$K2=MIN($K2:$K32)

Range C29:K55 =$K29=MAX($K29:$K55)
Range C29:K55 =$K29=MIN($K29:$K55)

Range C56:K82 =$K56=MAX($K56:$K82)
Range C56:K82 =$K56=MIN($K56:$K82)

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Seriol Num	Part Num	Year	Seriol	Row Num	Sum	Sum Rank	Delay	Delay Rank
2			1	26184	19/20	37	2.267	176​	81	77	50
3			2	1905	18/19	51	2.212	72​	77	132	61
4			3	25452	20/21	37	2.326	71​	75	18	17
5			4	78688	19/20	34	2.264	99​	80	80	51
6			5	12678	19/20	39	2.269	39​	66	75	49
7			6	25777	17/18	42	2.136	27​	50	208	69
8			7	72839	20/21	50	2.339	71​	75	5	6
9			8	72427	17/18	10	2.104	38​	65	240	72
10			9	19998	19/20	50	2.280	29​	54	64	45
11			10	15418	20/21	54	2.343	79​	78	1	2
12			11	33165	19/20	21	2.251	35​	60	93	53
13			12	29449	20/21	22	2.311	41​	70	33	30
14			13	31643	20/21	24	2.313	48​	72	31	28
15			14	79859	20/21	46	2.335	26​	47	9	10
16			15	11651	20/21	5	2.294	21​	37	50	39
17			16	41969	20/21	40	2.329	35​	60	15	14
18			17	94305	20/21	1	2.290	20​	34	54	41
19			18	86136	19/20	58	2.288	24​	43	56	42
20			19	59290	20/21	47	2.336	65​	74	8	9
21			20	5118	18/19	19	2.180	26​	47	164	64
22			21	82866	20/21	34	2.323	30​	57	21	20
23			22	44082	20/21	49	2.338	35​	60	6	7
24			23	42728	20/21	9	2.298	17​	28	46	37
25			24	44191	04/05	41	1.433	15​	21	911	81
26			25	11561	20/21	18	2.307	29​	54	37	32
27			26	54217	20/21	26	2.315	19​	32	29	27
28			27	78547	17/18	33	2.127	16​	25	217	70
29			28	26184	20/21	51	2.340	82​	79	4	5
30			29	1905	19/20	43	2.273	26​	47	71	48
31			30	25452	18/19	44	2.205	40​	69	139	62
32			31	78688	18/19	36	2.197	42​	71	147	63
33			32	12678	20/21	38	2.327	21​	37	17	16
34			33	25777	20/21	29	2.318	22​	40	26	24
35			34	72839	19/20	57	2.287	34​	58	57	43
36			35	72427	19/20	16	2.246	16​	25	98	54
37			36	19998	20/21	11	2.300	20​	34	44	35
38			37	15418	19/20	3	2.233	39​	66	111	57
39			38	33165	20/21	48	2.337	21​	37	7	8
40			39	29449	19/20	6	2.236	25​	45	108	56
41			40	31643	18/19	63	2.224	25​	45	120	58
42			41	79859	11/12	20	1.760	10​	11	584	79
43			42	11651	14/15	35	1.936	9​	10	408	77
44			43	41969	16/17	28	2.055	13​	16	289	75
45			44	94305	17/18	47	2.141	11​	13	203	68
46			45	86136	15/16	41	1.998	5​	2	346	76
47			46	59290	20/21	55	2.344	37​	63	0	1
48			47	5118	18/19	58	2.219	13​	16	125	60
49			48	82866	19/20	22	2.252	15​	21	92	52
50			49	44082	20/21	31	2.320	15​	21	24	22
51			50	42728	20/21	39	2.328	6​	4	16	15
52			51	44191	07/08	22	1.540	3​	1	804	80
53			52	11561	20/21	36	2.325	24​	43	19	18
54			53	54217	16/17	57	2.084	7​	6	260	74
55			54	78547	20/21	17	2.306	11​	13	38	33
56			55	26184	20/21	41	2.330	63​	73	14	13
57			56	1905	20/21	28	2.317	28​	52	27	25
58			57	25452	20/21	42	2.331	39​	66	13	12
59			58	78688	20/21	45	2.334	37​	63	10	11
60			59	12678	16/17	59	2.086	10​	11	258	73
61			60	25777	19/20	47	2.277	22​	40	67	46
62			61	72839	20/21	33	2.322	27​	50	22	21
63			62	72427	18/19	10	2.171	22​	40	173	66
64			63	19998	20/21	27	2.316	17​	28	28	26
65			64	15418	19/20	13	2.243	34​	58	101	55
66			65	33165	20/21	10	2.299	19​	32	45	36
67			66	29449	19/20	45	2.275	16​	25	69	47
68			67	31643	20/21	20	2.309	28​	52	35	31
69			68	79859	19/20	56	2.286	15​	21	58	44
70			69	11651	20/21	23	2.312	7​	6	32	29
71			70	41969	20/21	53	2.342	20​	34	2	3
72			71	94305	17/18	23	2.117	7​	6	227	71
73			72	86136	17/18	55	2.149	7​	6	195	67
74			73	59290	20/21	16	2.305	29​	54	39	34
75			74	5118	20/21	30	2.319	14​	20	25	23
76			75	82866	18/19	62	2.223	17​	28	121	59
77			76	44082	20/21	8	2.297	13​	16	47	38
78			77	42728	20/21	52	2.341	13​	16	3	4
79			78	44191	12/13	9	1.805	5​	2	539	78
80			79	11561	20/21	4	2.293	11​	13	51	40
81			80	54217	18/19	17	2.178	6​	4	166	65
82			81	78547	20/21	35	2.324	18​	31	20	19
83
84

Result image is attached

Have a nice time

Kind regards,
Moti

Toadstool, I apologize for my error, as Peter_SSs suggested and corrected my error in the #Post7, your Formula worked absolutely great ?

Thank you for your help. Have a nice time

Kind regards,
Moti

 

[Toadstool]

You're welcome!