SOLVED

conditional formatting to highlight number of cells based on count

%3CLINGO-SUB%20id%3D%22lingo-sub-1485227%22%20slang%3D%22en-US%22%3Econditional%20formatting%20to%20highlight%20number%20of%20cells%20based%20on%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485227%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%2C%3C%2FP%3E%3CP%3EI've%20attached%20my%20production%20plan%20file.%20i%20need%20to%20highlight%20cells%20based%20on%20shifts%20taking%20for%20the%20production.%20as%20a%20example%20if%20i%20have%20100%2C000%20pcs%20to%20produce%20and%20my%20capacity%20is%20250%2C000%20per%20shift.%20it%20means%20i%20need%204%20shifts%20to%20production.%20i%20need%20to%20highlight%204%20cells%20Based%20on%20days%20to%20be%20taken%20for%20production.%20please%20refer%20xlsx%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20anyone%20help%20to%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1485227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1485477%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20to%20highlight%20number%20of%20cells%20based%20on%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3BSee%20attached.%20Cells%20with%20blue%20text%20require%20input.%20All%20others%20calculate%20automatically.%20Note%20that%20I%20use%20real%20dates%20in%20the%20column%20headers%2C%20aligned%20to%20center%20across%20two%20cells%20(i.e.%20NOT%20merged).%20Start%20date%20includes%20a%20time%20element%20as%20well%20to%20help%20calculate%20the%20end%20date%20and%20time%20in%20F.%20The%20example%20only%20covers%204%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%20than%20applying%20multiple%26nbsp%3B%3CSPAN%3Econditional%20formatting%20formulae%2C%20I%20chose%20to%20test%20the%20condition%20in%20columns%20G%3AN%2C%20resulting%20in%20TRUE%20or%20FALSE%20(formatted%20white%20text%20on%20white%20background)%2C%20thus%20invisible%20and%20then%20apply%20conditional%20formatting%20on%20the%20entire%20row%20area%20with%20one%20simple%20rule%20(TRUE%20%3D%20Green%20text%20and%20background).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20feasible%20in%20your%20situation.%20If%20so%2CI%20trust%20you%20can%20apply%20this%20method%20to%20your%20own%20schedule%20yourself.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1485543%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20to%20highlight%20number%20of%20cells%20based%20on%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20thank%20you%20very%20much%20for%20your%20respond%20.%20its%20really%20good.%20i%20have%20small%20trouble.%20if%201st%20production%20end%20at%20day%20shift%20how%20can%20i%20start%20same%20day%20night%20shift%20production%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1485623%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20to%20highlight%20number%20of%20cells%20based%20on%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3BChanged%20the%20formula%20a%20little%20bit.%20Forgot%20to%20add%20%22%2B12%2F24%22%20to%20the%20first%20part%20of%20teh%20Night-shift%20formulae.%20See%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1485796%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20to%20highlight%20number%20of%20cells%20based%20on%20count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bmany%20thanx%20you're%20great%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

hi,

I've attached my production plan file. i need to highlight cells based on shifts taking for the production. as a example if i have 100,000 pcs to produce and my capacity is 250,000 per shift. it means i need 4 shifts to production. i need to highlight 4 cells Based on days to be taken for production. please refer xlsx file

 

can anyone help to me?

4 Replies
Highlighted

@Ravindu94 See attached. Cells with blue text require input. All others calculate automatically. Note that I use real dates in the column headers, aligned to center across two cells (i.e. NOT merged). Start date includes a time element as well to help calculate the end date and time in F. The example only covers 4 days.

 

Rather than applying multiple conditional formatting formulae, I chose to test the condition in columns G:N, resulting in TRUE or FALSE (formatted white text on white background), thus invisible and then apply conditional formatting on the entire row area with one simple rule (TRUE = Green text and background).

 

Perhaps feasible in your situation. If so,I trust you can apply this method to your own schedule yourself.

Highlighted

@Riny_van_Eekelen  thank you very much for your respond . its really good. i have small trouble. if 1st production end at day shift how can i start same day night shift production 

Highlighted
Solution

@Ravindu94 Changed the formula a little bit. Forgot to add "+12/24" to the first part of teh Night-shift formulae. See attached

 

Highlighted