Conditional Formatting...

sumospim

New Member
Joined
Jul 21, 2013
Messages
21
Hi all, i wonder if someone could help me on the above?

I want to automatically place a colour in a cell for the following instances.

  1. When a cell is between a certain range ( +/- 7.5% of a value) (CORE range)
  2. When a cell is between +7.5% and 15% of a value (HIGH range)
  3. When a cell is between -7.5% and -15% of a value (LOW range)
  4. When a cell is > than 15% of a value (SUPERHIGH range)
  5. When a cell is < than 15% of a value. (SUPERLOW range)

Any guidance would be greatly appreciated.

Best wishes

Simon
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

alvin-chung

Active Member
Joined
Nov 24, 2013
Messages
361
Hi Simon, assume your cell is in A2 and the value is in B2 you can create new conditional formatting formula such as =ABS((A2-B2)/B2)<0.075) for your first condition; and similarly for the rest...
 

sumospim

New Member
Joined
Jul 21, 2013
Messages
21

ADVERTISEMENT

C10 - I10 (Row marked HCL)

Cheers
 

alvin-chung

Active Member
Joined
Nov 24, 2013
Messages
361
But there's no value in those cells, probably you've replaced the values with the expected conditional formatting?
 

sumospim

New Member
Joined
Jul 21, 2013
Messages
21

ADVERTISEMENT

Sorry, so..

The conditional format colour needs to go in cells C10-I10 but it refers to the value in C7

So C7 is in the range of low as per spreadsheet so C10 should automatically go to Low (yellow) and so on...
 

alvin-chung

Active Member
Joined
Nov 24, 2013
Messages
361
No worries, just need clarification because the colour you show C10:I10 seems inaccurate with your criteria :)
For LOW range, try select C10:I10 and create new conditional formatting formula below will yellow fill
Code:
=AND(C7>=$L$5*0.075,C7<=$L$5*0.95)

You shall see C10, D10 and I10 highlighted as yellow?
 

sumospim

New Member
Joined
Jul 21, 2013
Messages
21
That makes perfect sense but how do i cover all the conditions accross the range C10:I10, so that all cells in that range change to meet the criteria as below:

So Core range is between 16960 - 19710
High range is between 19710 - 21085
Low range is between 16960 - 15584
Super high is > 21085
Super low is < 15584

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Thanks so much for your patience...
 

alvin-chung

Active Member
Joined
Nov 24, 2013
Messages
361
Correction to earlier formula, please create and try all the Conditional Formatting below:
ps: some of your range are overlapping and I've tune that a bit (please change according to your criteria)

Super High
Code:
=C7>$L$5*1.15

High
Code:
=AND(C7>$L$5*1.075,C7<=$L$5*1.15)

Core
Code:
=AND(C7>=$L$5*0.925,C7<=$L$5*1.075)

Low
Code:
=AND(C7>=$L$5*0.75,C7<$L$5*0.925)

Super Low
Code:
=C7<$L$5*0.75
 

Forum statistics

Threads
1,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

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