Conditional formatting based on other cell value

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
Hi is it possible to hightlight top 3 values in column 'A' but only if cell 'B1' is value of 1 ?
Thanks

Book1.xlsx
ABCDE
11
2
3
4
56
67
712
80
94
105
1118
122
133
144
15
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5:A14Cell Valuetop 3 valuestextNO
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows
With a formula used as a custom rule.

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

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
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
ABCD
11
2
3
4
51
62
73
84
95
106
117
128
139
1410
15
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACellcontains a blank value textYES
A:AExpression=AND($B$1=1,A5>=LARGE($A$5:$A$14,3))textNO
 

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

not sure why it does not work when calculations are set to manual tho
because conditional formatting works on calculation.
 

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
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
ABC
11
2
3
41
52
612
74
85
96
107
118
129
13
14
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5:A14Expression=AND($B$1=1,A5>=LARGE($A$5:$A$14,3))textNO
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 31, 2020
Messages
346
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top