Conditional Formatting

[mystic19]

Hi, i'm quite new to excel and wanting to learn and understand it better. I'm struggling on how to conditional format the "Volume Available" column. Ideally what I want is for the cells to be highlighted a colour such as orange if it is less than 20% than the total volume. This is shown in the "Stock Volume" column.

If anyone can suggest a way to link the two column together that would be great or advise another solution. Thanks!

 

[Burrgogi]

1. Select all the numbers in col. C starting from row 4.
2. In the Home tab, click on 'Conditional Formatting'. From the drop down menu, select "New Rule".
3. In the new window that pops up, select: "Use a formula to determine which cells to format"
4. In the box below that, type in the following formula.
=C2/B2<.2
5. Next click on the 'Format' button.
6. Choose whatever color you want.
7. Click OK on your way out.

Screenshot as an example:

 

[rlv01]

deally what I want is for the cells to be highlighted a colour such as orange if it is less than 20% than the total volume. This is shown in the "Stock Volume" column.

Possibly something like this.

Book1
	A	B	C
1	IHC ANTIBODY STOCK LIST
2	Anitbody	Stock Volume	Volume available
3	tigellus	1000uL	700uL
4	paralalia	1000uL	130uL
5	llanero	7000uL	4690uL
6	kwarterka	1000uL	110uL
7	toxone	1000uL	340uL
8	interhyal	1000uL	80uL
9	wingcut	1000uL	750uL
10	mattapony	7000uL	1050uL
11	heartdeep	7000uL	5250uL
12	bellwind	1000uL	100uL
13	melipona	1000uL	380uL
14	vestryize	7000uL	560uL
15	seroon	7000uL	2800uL
16	reburden	7000uL	1050uL
17	thiazide	1000uL	410uL
18	ingan	7000uL	700uL
19	sestine	7000uL	2660uL
20	upwork	7000uL	2870uL
21	downlast	1000uL	290uL
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A21	Expression	=VALUE(LEFT(C3,LEN(C3)-2))<VALUE(LEFT(B3,LEN(B3)-2))*20/100	text	NO

 

[Burrgogi]

The OP said he wants to format col. C labeled as "Volume Available".

 

[mystic19]

1. Select all the numbers in col. C starting from row 4.
2. In the Home tab, click on 'Conditional Formatting'. From the drop down menu, select "New Rule".
3. In the new window that pops up, select: "Use a formula to determine which cells to format"
4. In the box below that, type in the following formula.
=C2/B2<.2
5. Next click on the 'Format' button.
6. Choose whatever color you want.
7. Click OK on your way out.

Screenshot as an example:

View attachment 110605

That worked perfectly! Thank you very much! Much appreciated!

 

[Burrgogi]

That worked perfectly! Thank you very much! Much appreciated!

You're welcome. If my post answered your question, I would kindly ask that you check the green button next to my post.