if with some condition

[Vishaal]

Hi all,

we have the following sheet, and want to change the color if the condition meet according to E

MAX LEVEL	Quantity		Extra
1500	1600		Normal
500	350		60 - 40% balance
200	71		BELOW 30%
100	25

help pls

 

[mamady]

Hi,

You may follow the below steps:

• from the home ribbon, click on Conditional Formatting >> New Rule
• Choose "Format only cells that contain"
• put the desired value brackets (between), (less than) or (greater than)
• Press Format to select the desired color and font
• repeat the process for other value brackets

Best Regards
M. Yusuf

 

[Toadstool]

Hi Vishaal,

I'm confused by a few things:

1. Your "Extra" heading looks like it belongs on the row below so I've moved all conditional formats down one row
2. 71 is 35.5% of 200 so that row doesn't seem to meet the criteria. I've added another row of data to trigger the 40%-60% rule
3. Because there are gaps (30% to 40%?) I don't know if that should be "Normal" or blank. I've left those blank.

Does this do what you want?

Book1
	B	C	D	E	F
1	MAX LEVEL	Qty
2	1500	1600
3	500	350
4	200	71
5	100	25
6	55	25
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E6	Expression	=C2/B2<=0.3	text	NO
E2:E6	Expression	=AND(C2/B2<=0.6,C2/B2>=0.4)	text	NO
E2:E6	Expression	=AND(C2<=B2,C2/B2>0.6)	text	NO
E2:E6	Expression	=C2>B2	text	NO

 

[Vishaal]

Thanks Toadstool,

very good formula, its working in my case, thanks for your help

 

[Vishaal]

Hi Toadstool,

Pls guide me how can we copy this in other column, because when we do that its not changing the cell ref

I have also started a new post for this query

Thanks

 

[Toadstool]

I replied to your new post.

 

[Vishaal]

Thanks sir,
Checking my new post