Conditional Formatting...

sumospim

New Member
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

alvin-chung

Active Member
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
HI Alvin, cheers for your response, I am getting errors in that formula.

Could you take a look at this link, may help you, help me I have put spreadsheet and explanation.

https://app.box.com/s/bzbi7d8anolbibulhg2w

alvin-chung

Active Member
Hi, which range of cells you are trying to highlight?

sumospim

New Member
C10 - I10 (Row marked HCL)

Cheers

alvin-chung

Active Member
But there's no value in those cells, probably you've replaced the values with the expected conditional formatting?

sumospim

New Member
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
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
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
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``

Replies
1
Views
78
Replies
2
Views
76
Replies
6
Views
163
Replies
11
Views
151
Replies
2
Views
73

1,172,173
Messages
5,879,463
Members
433,434
Latest member
skk0048

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?

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

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