Conditional Formatting based on ranges

Jones54

New Member
Joined
Jan 25, 2016
Messages
14
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
293
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
293
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Your welcome. Glad I could help.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,640
Members
410,696
Latest member
JTrehan
Top