Conditional Formatting based on ranges

Jones54

New Member
Joined
Jan 25, 2016
Messages
19
Hi all.

I am having an issue with conditional formatting I have tried all possible options but without success. I have a workbook which provides info. on production data for a shift and I want to show whether work produced is below target, between a range of 80% efficient and 100% efficient and above the 100% efficiency (if this happens it will trigger an investigation as should not be possible based on cycle time). I have set the worksheet up so that if a certain product is picked from a dropdown box in one cell then in another cell the target (at 80%) is automatically filled in. For example if
GLOW 2.0 - LOWER HOUSING SUB ASSY (TOPLINE)
is picked then 2255 is shown in another cell (which is 80% of what can be achieved using the cycle time). What I want to do is if total products produced is below 2255 then with conditional formatting I would fill the cell "Red", if the total produced is between 2255 and 2819 then the cell would be formatted "Green", if the total produced is above 2819 then the cell would be formatted "Gold". This I can do no problem. What I cannot resolve is if I pick a different product the figures of 2255, 2819 will be different as the cycle time would be different therefore giving a different set of figures, eg if I pick
GLOW 2.0 - STS LENS (STANDARD) then the target will be 6135 with an upper target of 7669, the conditional formatting then doesn't work. I believe the conditional formatting should be based on the product picked and the associated target figures but I cannot figure it out.

I hope I have explained it well enough.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
You could set up your data like below:
1601306577892.png

Then set up the conditional formatting on G2 as follows:
Cell Value <$H$2 = Red
Cell Value Between $H$2 and $I$2 = Green
Cell Value > $I$2 = Gold

Would that work?
 
Upvote 0

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

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