Conditional Formatting

aarti_rto

New Member
Joined
Nov 29, 2019
Messages
49
Office Version
  1. 2016
Platform
  1. Windows
  2. Web
Hi,
i have below data of what should be the standard minimum & maximum stock and actual available stock of the part.
i need to know how to highlight using conditional formatting the parts as conditions given below.
please help.

Part NoMin StockMax StockActual Avl Stock
A
100​
110​
90​
B
80​
210​
205​
C
80​
310​
136​
conditions for formatting
Highlight "actual avl stock & Part No " in green if the "actual avl stock" is within the limit i.e. "min & Max"
Highlight "actual avl stock & Part No " in Red if the "actual avl stock" is less than "min stock"
Highlight "actual avl stock & Part No " in Yellow if the "actual avl stock" is more than "Max stock"
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try this. Select A2:Axx and apply the Conditional Formatting rules shown. Then select A2:Axx and use the Format Painter to apply to D2:Dxx

Book1
ABCD
1Part NoMin StockMax StockActual Avl Stock
2A10011090
3B80210205
4C80310336
CF Stock
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A4Expression=$D2=MEDIAN($B2:$D2)textNO
A2:A4Expression=$D2<$B2textNO
A2:A4Expression=$D2>$C2textNO
 
Upvote 0
Try this. Select A2:Axx and apply the Conditional Formatting rules shown. Then select A2:Axx and use the Format Painter to apply to D2:Dxx

Book1
ABCD
1Part NoMin StockMax StockActual Avl Stock
2A10011090
3B80210205
4C80310336
CF Stock
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A4Expression=$D2=MEDIAN($B2:$D2)textNO
A2:A4Expression=$D2<$B2textNO
A2:A4Expression=$D2>$C2textNO
hi,
can you please share video , i am using excel 2016 & unable to do the above said process.
thanks
 
Upvote 0
Try this. Select A2:Axx and apply the Conditional Formatting rules shown. Then select A2:Axx and use the Format Painter to apply to D2:Dxx

Book1
ABCD
1Part NoMin StockMax StockActual Avl Stock
2A10011090
3B80210205
4C80310336
CF Stock
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A4Expression=$D2=MEDIAN($B2:$D2)textNO
A2:A4Expression=$D2<$B2textNO
A2:A4Expression=$D2>$C2textNO
Thanks, got it.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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
Back
Top