Home

Conditional Formatting Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-711642%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI'm%20looking%20for%20help%20on%20a%20formula%20to%20apply%20conditional%20formatting%20to%20cells%20font%20color%20based%20on%20a%20cell%20%2B%2F-%20a%20tolerance%20in%20a%20first%20article%20sheet%20used%20in%20a%20metal%20or%20plastic%20manufacturing%20setting%2C%20can%20you%20help%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-711642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711663%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711663%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364003%22%20target%3D%22_blank%22%3E%40DanMRaycap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20give%20a%20sample%20how%20it%20looks%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711667%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711667%22%20slang%3D%22en-US%22%3E%3CP%3EYes%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711683%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711683%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E1*%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E%22%2B%2F-0.007%22%3C%2FTD%3E%3CTD%3E14.665%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.653%3C%2FTD%3E%3CTD%3E0.006708%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364003%22%20target%3D%22_blank%22%3E%40DanMRaycap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711687%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711695%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711695%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E%22%2B%2F-0.007%22%3C%2FTD%3E%3CTD%3E14.665%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711724%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711724%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364003%22%20target%3D%22_blank%22%3E%40DanMRaycap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20590px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119940i94D89E296409D85D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711990%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711990%22%20slang%3D%22en-US%22%3E%3CP%3EThank%26nbsp%3Byou%20that's%20close%2C%20so%20I'm%20trying%20to%20make%20any%20of%20the%205%20measurement%20cells%20turn%20red%20font%20IF%20they%20are%20Out%20of%20tolerance.%26nbsp%3B%20IE%26nbsp%3B%20A1%20%2B%2F-%20.0007%20%3D%20Red%20font%20IF%20not%20font%20stays%20Black.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDIMENSION%2FSPECIFICATION%3C%2FTD%3E%3CTD%3ESUPPLIER%20MEASUREMENTS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPART%201%3C%2FTD%3E%3CTD%3EPART%202%3C%2FTD%3E%3CTD%3EPART%203%3C%2FTD%3E%3CTD%3EPART%204%3C%2FTD%3E%3CTD%3EPART%205%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E%22%2B%2F-0.007%22%3C%2FTD%3E%3CTD%3E14.665%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712016%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364003%22%20target%3D%22_blank%22%3E%40DanMRaycap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhere%20is%20A1%20in%20this%20sample%3F%20And%20here%20only%20the%20cell%20under%20PART%202%20shall%20be%20in%20red%2C%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712028%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712028%22%20slang%3D%22en-US%22%3E%3CP%3EOk%20A1%20is%20Dimension%20lets%20make%20it%201.0%20and%20tolerance%20is%20%2B%2F-%20.0007.%3C%2FP%3E%3CP%3ESo%26nbsp%3BIF%20Measurement%20is%20In%20between%26nbsp%3B%200.993%E2%80%AC%20and%26nbsp%3B1.007%E2%80%AC%20Font%20in%20the%20cell%20should%20remain%2C%20Black%2C%20IF%20the%20dimension%20is%20Not%20in%20the%20range%20it%20should%20turn%20Red%20for%20all%205%20cells%20called%20supplier%20measurements%2C%20does%20that%20help%3F%26nbsp%3B%20%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712030%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712030%22%20slang%3D%22en-US%22%3E%3DIF(D10%26gt%3B(%24B%2410%2B%24C%2410)%2CRed%2C))IF(D10%26lt%3B(%24B%2410%2B%24C%2410)%2CRed%2C))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712078%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364003%22%20target%3D%22_blank%22%3E%40DanMRaycap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENope%2C%20that%20fully%20different%20from%20your%20first%20sample%20and%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20322px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119966iFB55A97C133107FE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIs%20that%20one%20is%20the%20basis%20for%20the%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712095%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712095%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EITEM%3C%2FTD%3E%3CTD%3EDIMENSION%2FSPECIFICATION%3C%2FTD%3E%3CTD%3ESUPPLIER%20MEASUREMENTS%3C%2FTD%3E%3CTD%3EAVERAGE%3C%2FTD%3E%3CTD%3ESTD%20DEV%3C%2FTD%3E%3CTD%3EACC%3C%2FTD%3E%3CTD%3EREJ%3C%2FTD%3E%3CTD%3EMETHOD%20or%20GAUGE%3C%2FTD%3E%3CTD%3EASSET%23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPART%201%3C%2FTD%3E%3CTD%3EPART%202%3C%2FTD%3E%3CTD%3EPART%203%3C%2FTD%3E%3CTD%3EPART%204%3C%2FTD%3E%3CTD%3EPART%205%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1*%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E%22%2B%2F-0.007%22%3C%2FTD%3E%3CTD%3E14.665%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.650%3C%2FTD%3E%3CTD%3E14.653%3C%2FTD%3E%3CTD%3E0.006708%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDC%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EIn%26nbsp%3Bthe%20example%20above%20Part%231%20should%20have%20Red%20font%20if%20the%20formula%20is%20working%20correctly.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712181%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712181%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364003%22%20target%3D%22_blank%22%3E%40DanMRaycap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOkay%2C%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20813px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119969iD10DC1ABAD297D10%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20A3%20we%20have%20text%20%221*%22%20and%20in%20C3%20text%20%22%2B%2F-0.007%22%2C%20in%20other%20cells%20in%20this%20row%20are%20numbers.%20Based%20on%20which%20formula%20we%20shall%20compare%20texts%20and%20numbers%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
DanMRaycap
Occasional Contributor

I'm looking for help on a formula to apply conditional formatting to cells font color based on a cell +/- a tolerance in a first article sheet used in a metal or plastic manufacturing setting, can you help?

13 Replies

@DanMRaycap 

Could you give a sample how it looks like?

 

1*14.650"+/-0.007"14.66514.65014.65014.65014.65014.6530.006708  

@DanMRaycap 

 

14.650"+/-0.007"14.66514.65014.65014.65014.650

@Sergei Baklan 

@DanMRaycap 

 

That could be like

image.png

Please check attached.

Thank you that's close, so I'm trying to make any of the 5 measurement cells turn red font IF they are Out of tolerance.  IE  A1 +/- .0007 = Red font IF not font stays Black.

 

DIMENSION/SPECIFICATIONSUPPLIER MEASUREMENTS
PART 1PART 2PART 3PART 4PART 5
14.650"+/-0.007"14.66514.65014.65014.65014.650

 

@Sergei Baklan 

@DanMRaycap 

 

Where is A1 in this sample? And here only the cell under PART 2 shall be in red, correct?

Ok A1 is Dimension lets make it 1.0 and tolerance is +/- .0007.

So IF Measurement is In between  0.993‬ and 1.007‬ Font in the cell should remain, Black, IF the dimension is Not in the range it should turn Red for all 5 cells called supplier measurements, does that help?   @Sergei Baklan 

=IF(D10>($B$10+$C$10),Red,))IF(D10<($B$10+$C$10),Red,))

@DanMRaycap 

 

Nope, that fully different from your first sample and looks like

image.png

Is that one is the basis for the formula?

 

ITEMDIMENSION/SPECIFICATIONSUPPLIER MEASUREMENTSAVERAGESTD DEVACCREJMETHOD or GAUGEASSET#
PART 1PART 2PART 3PART 4PART 5
1*14.650"+/-0.007"14.66514.65014.65014.65014.65014.6530.006708  DC 

In the example above Part#1 should have Red font if the formula is working correctly. @Sergei Baklan 

@DanMRaycap 

Okay, like this

image.png

In A3 we have text "1*" and in C3 text "+/-0.007", in other cells in this row are numbers. Based on which formula we shall compare texts and numbers?