Change colors based on values of other cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2707243%22%20slang%3D%22en-US%22%3EChange%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2707243%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20need%20some%20help%20with%20excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20are%20images%20of%20the%20worksheet%20template%20I%20use.%20For%20every%20project%2C%20I%20start%20with%20a%20blank%20template%20and%20then%20input%20different%20values%20based%20on%20my%20testing%2C%20as%20can%20be%20seen%20in%20the%20second%20and%20third%20images.%20The%20initial%20RETAIN%20sample%20is%20the%20baseline%20for%20the%20project%20and%20how%20much%20deviation%20there%20is%20from%20it%2C%20gives%20us%20an%20idea%20of%20how%20the%20product%20is%20coming%20along.%20Basically%2C%20I%20want%20to%20be%20able%20to%20set%20up%20a%20formula%20on%20the%20template%2C%20so%20that%20whenever%20I%20reuse%20it%2C%20if%20one%20project's%20pH%20is%205.0%20and%20the%20visc%20is%2024%2C000%2C%20but%20another%20has%20a%20pH%20of%208.5%20and%20a%20visc%20of%2080%2C000%2C%20that%20the%20values%20and%20I%20input%20in%20the%20subsequent%20months%20change%20colors%20going%20off%20the%20RETAIN%20samples.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20pH%2C%20using%204.0%20as%20an%20example%2C%20if%20it%20goes%20under%203.0%20or%20above%205.0%2C%20I%20want%20it%20to%20change%20colors%2C%20but%20if%20I%20reuse%20the%20template%20for%20a%20different%20project%20and%20the%20pH%20is%207.5%20instead%2C%20I%20want%20it%20to%20change%20colors%20if%20goes%20under%206.5%20and%20above%207.5.%20Conversely%20for%20visc%2C%20if%20the%20RETAIN%20is%2050%2C000%2C%20I%20want%20it%20to%20change%20colors%20if%20it%20goes%20under%2040%2C000%20or%20above%2060%2C000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20any%20of%20this%20possible%3F%20What%20do%20I%20have%20to%20do%20in%20order%20to%20input%20these%20formulas%20into%20the%20template%20so%20that%20it's%20automatic%20from%20now%20on%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22worksheet%20template%20example%202.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307287i1685E6C9CECE993E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22worksheet%20template%20example%202.PNG%22%20alt%3D%22worksheet%20template%20example%202.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22worksheet%20template%20example.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307285i56214D0AE21733E9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22worksheet%20template%20example.PNG%22%20alt%3D%22worksheet%20template%20example.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22worksheet%20template.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307284i4B063EFD123BB651%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22worksheet%20template.PNG%22%20alt%3D%22worksheet%20template.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2707243%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2708575%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2708575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1143450%22%20target%3D%22_blank%22%3E%40Oscar_Santos%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20conditional%20formatting%3CBR%20%2F%3E%26gt%3B%26gt%3B%20formula%20to%20determine%20wich%20cells%20to%20format%3CBR%20%2F%3EReplace%20MeasureValue%20and%20RetainValue%20for%20your%20Cell%20reference%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAnd(MeasuredValue%26lt%3B%26gt%3B%22%22%2COR(MerasuredValue%26lt%3B%3DRetainValue-1%2CMerasuredValue%26gt%3B%3DRetainValue%2B1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3DAND(%24B%243%26lt%3B%26gt%3B%22%22%2COR(%24B%243%26lt%3B%3D%24B%242-1%2C%24B%243%26gt%3B%3D%24B%242%2B1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2711199%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2711199%22%20slang%3D%22en-US%22%3EI'm%20sorry%2C%20I%20don't%20think%20I%20fully%20understand%20the%20instructions%3F%20My%20experience%20with%20excel%20is%20only%20with%20the%20basics.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2711711%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2711711%22%20slang%3D%22en-US%22%3ECan%20you%20please%20share%20a%20sample%20File%20such%20like%20your%20screenshots%3F%20As%20you%20mentioned%2C%20you%20are%20having%20some%20difficulties%20in%20understand%20my%20step-by-step.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712203%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3BHere%20is%20a%20copy%20of%20the%20template.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712693%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1143450%22%20target%3D%22_blank%22%3E%40Oscar_Santos%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712773%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712773%22%20slang%3D%22en-US%22%3EThank%20you!%20This%20is%20very%20close%20to%20what%20I%20want!%20Can%20you%20explain%20how%20I%20can%20change%20the%20range%3F%20Like%2C%20lets%20say%20I%20want%20it%20to%20be%20a%20difference%20of%202.0%20instead%2C%20as%20in%2C%20if%20the%20RETAIN%20value%20for%20pH%20is%204.0%2C%20it%20changes%20if%20it's%202.0%20or%206.0%3F%20How%20do%20you%20I%20change%20that%20in%20the%20formula%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712789%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712789%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20are%202%20Options%3C%2FP%3E%3CP%3EYou%20change%20straight%20away%20in%20the%20formula%3C%2FP%3E%3CP%3E%3DAND(B12%26lt%3B%26gt%3B%22%22%3BOR(B12%26lt%3B%3DB%249-YourFactorValue%3BB12%26gt%3B%3DB%249%2BYourFactorValue))%3C%2FP%3E%3CP%3EOr%20you%20can%20add%20the%20value%20in%20some%20column%20of%20the%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2728517%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20colors%20based%20on%20values%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2728517%22%20slang%3D%22en-US%22%3ESorry%20to%20keep%20bothering%20you%2C%20I%20was%20trying%20to%20figure%20out%20how%20to%20change%20the%20formula%20itself%20but%20I%20couldn't%20figure%20it%20out.%20My%20current%20problem%20is%20that%20the%20values%20for%20Viscosity%20are%20much%20higher%20than%20pH.%20pH%20goes%20from%20a%20range%20of%201.0%20-%2012.0%2C%20while%20viscosity%20can%20be%20anywhere%20from%20500%20-%20100%2C000.%20Can%20you%20please%20also%20add%20a%20reference%20value%20for%20viscosity%20aswell%3F%20I%20appreciate%20all%20the%20work%20you've%20done%20for%20me%20so%20far%2C%20it%20has%20been%20helping%20me%20tremendously.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, I need some help with excel. 

 

Below are images of the worksheet template I use. For every project, I start with a blank template and then input different values based on my testing, as can be seen in the second and third images. The initial RETAIN sample is the baseline for the project and how much deviation there is from it, gives us an idea of how the product is coming along. Basically, I want to be able to set up a formula on the template, so that whenever I reuse it, if one project's pH is 5.0 and the viscosity is 24,000, but another has a pH of 8.5 and a visc of 80,000, that the values and I input in the subsequent months change colors going off the RETAIN samples.

 

So for pH, using 4.0 as an example, if it goes under 3.0 or above 5.0, I want it to change colors, but if I reuse the template for a different project and the pH is 7.5 instead, I want it to change colors if it goes under 6.5 and above 8.5. Conversely for viscosity, if the RETAIN is 50,000, I want it to change colors if it goes under 40,000 or above 60,000.

 

Is any of this possible? What do I have to do in order to input these formulas into the template so that it's automatic from now on?

 

Also, my experience with Excel is only with the basics, I apologize if I don't fully grasp the answers to my question right away.

 

 

 

worksheet template example 2.PNG

 

worksheet template example.PNG

worksheet template.PNG

8 Replies

@Oscar_Santos 

On conditional formatting
>> formula to determine wich cells to format
Replace MeasureValue and RetainValue for your Cell reference

=And(MeasuredValue<>"",OR(MerasuredValue<=RetainValue-1,MerasuredValue>=RetainValue+1))

=AND($B$3<>"",OR($B$3<=$B$2-1,$B$3>=$B$2+1))

I'm sorry, I don't think I fully understand the instructions? My experience with excel is only with the basics.
Can you please share a sample File such like your screenshots? As you mentioned, you are having some difficulties in understand my step-by-step.

@Juliano-Petrukio Here is a copy of the template.

@Oscar_Santos 

Find attached

Thank you! This is very close to what I want! Can you explain how I can change the range? Like, lets say I want it to be a difference of 2.0 instead, as in, if the RETAIN value for pH is 4.0, it changes if it's 2.0 or 6.0? How do you I change that in the formula?

There are 2 Options

You change straight away in the formula

=AND(B12<>"";OR(B12<=B$9-YourFactorValue;B12>=B$9+YourFactorValue))

Or you can add the value in some column of the spreadsheet.

 

Find attached

Sorry to keep bothering you, I was trying to figure out how to change the formula itself but I couldn't figure it out. My current problem is that the values for Viscosity are much higher than pH. pH goes from a range of 1.0 - 12.0, while viscosity can be anywhere from 500 - 100,000. Can you please also add a reference value for viscosity aswell? I appreciate all the work you've done for me so far, it has been helping me tremendously.