Conditional Formatting based on ranges

[Jones54]

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.

 

[sparky2205]

You could set up your data like below:

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?

 

[Jones54]

Thanks sparky2205 works great. Appreciate the help

 

[sparky2205]

Your welcome. Glad I could help.