Conditional formatting based on other cell value

[ste33uka]

Hi is it possible to hightlight top 3 values in column 'A' but only if cell 'B1' is value of 1 ?
Thanks

Book1.xlsx
	A	B	C	D	E
1		1
2
3
4
5	6
6	7
7	12
8	0
9	4
10	5
11	18
12	2
13	3
14	4
15
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:A14	Cell Value	top 3 values	text	NO

 

[jasonb75]

With a formula used as a custom rule.

=AND($B$1=1,A5>=LARGE($A$5:$A$14,3))

 

[ste33uka]

Thanks for the help again jason
its highlighting wrong cells tho,
and as i have have calculations set to manual, it doesnt work when workbook is saved,which is strange.

Book1.xlsx
	A	B	C	D
1		1
2
3
4
5	1
6	2
7	3
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell	contains a blank value	text	YES
A:A	Expression	=AND($B$1=1,A5>=LARGE($A$5:$A$14,3))	text	NO

 

[ste33uka]

With a formula used as a custom rule.

=AND($B$1=1,A5>=LARGE($A$5:$A$14,3))

Ok it works as in lightlghting top 3, but doesnt work when calcultions are set to manual

 

[jasonb75]

The rule is for A5:A14, you applied it to the whole column so it is going to be wrong.

 

[ste33uka]

The rule is for A5:A14, you applied it to the whole column so it is going to be wrong.

Thanks, not sure why it does not work when calculations are set to manual tho, thanks again

 

[jasonb75]

not sure why it does not work when calculations are set to manual tho

because conditional formatting works on calculation.

 

[ste33uka]

Could someone tell me how i can change the range of conditional format so it only looks at cells A4,A6,A8,A10,A12,A14 please.

Book1
	A	B	C
1		1
2
3
4	1
5	2
6	12
7	4
8	5
9	6
10	7
11	8
12	9
13
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:A14	Expression	=AND($B$1=1,A5>=LARGE($A$5:$A$14,3))	text	NO

 

[jasonb75]

Maybe

=AND($B$1=1,A5>=LARGE($A$5:$A$14,3),MOD(ROW($A$5:$A$14,2)=0)

=0 at the end for even numbered rows, =1 for odd numbered rows.

Also, make sure that all ranges are set correctly, you mention A4 in your list of cells but previously the range was starting at A5.

 

[ste33uka]

Maybe

=AND($B$1=1,A5>=LARGE($A$5:$A$14,3),MOD(ROW($A$5:$A$14,2)=0)

=0 at the end for even numbered rows, =1 for odd numbered rows.

Also, make sure that all ranges are set correctly, you mention A4 in your list of cells but previously the range was starting at A5.

I get error, youve enetered to many argyments for this function