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?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies