Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-1444286%22%20slang%3D%22en-US%22%3EFormulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444286%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%20everyone%2C%20I'm%20new%20here%20and%20I%20would%20like%20it%20if%20you%20help%20me%20with%20excel%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20from%20excel%20to%20color%20a%20cell%20if%20these%203%20words%20are%20not%20writing%20on%20it%20basic%2C%20Moyenne%20and%20complete%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20other%20words%2C%20if%20I%20type%20a%20word%20except%20for%20basic%20or%20Moyenne%20or%20complete%20the%20cell%20will%20be%20colored%20with%20red%20and%20the%20word%20in%20the%20white%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20can%20you%20help%20me%20with%20this%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20my%20email%20%3A%20%3CA%20href%3D%22mailto%3Ashevaa262%40gmail%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eshevaa262%40gmail.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1444286%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%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%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444329%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690698%22%20target%3D%22_blank%22%3E%40abdou_el96%3C%2FA%3E%26nbsp%3BI%20just%20joined%20this%20community.%20You%20can%20use%20conditional%20formatting%20%26gt%3B%20highlight%20cell%20rules%20%26gt%3B%20More%20rules%20%26gt%3B%20Select%20the%20preference%20and%20set%20the%20rules.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1444481%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1444481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690698%22%20target%3D%22_blank%22%3E%40abdou_el96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20only%20one%20of%20the%20excluded%20words%20could%20show%20up%20in%20a%20cell%2C%20select%20the%20whole%20data%20range%2C%20use%20Conditional%20Formatting%20with%20formula%20rule%20%3DXOR(ISERROR(SEARCH(%22basic%22%2CF2))%2CISERROR(SEARCH(%22moyenne%22%2CF2))%2CISERROR(SEARCH(%22complete%22%2CF2)))%20and%20format%20red%20fill%20with%20white%20text%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20the%20excluded%20words%20may%20show%20up%20at%20the%20same%20time%20in%20a%20cell%2C%20select%20whole%20data%20range%2C%20choose%20red%20fill%20with%20white%20text.%20Then%20apply%20Conditional%20formatting%20with%20formula%20rule%20%3DOR(IFERROR(SEARCH(%22basic%22%2C%24A2)%26gt%3B0%2C0)%2CIFERROR(SEARCH(%22moyenne%22%2C%24A2)%26gt%3B0%2C0)%2CIFERROR(SEARCH(%22complete%22%2C%24A2)%26gt%3B0%2C0))%20and%20format%20no%20fill%20with%20automatic%20text%20color%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20either%20solution%20works%20for%20your%20purpose%2C%20simply%20copy%20cell%20F2%20(solution%201)%20or%20A2%20(solution%202)%20in%20the%20attached%20workbook%20and%20paste%20special%20format%20to%20anywhere%20you%20want%20to%20apply%20the%20rule%20and%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

hello, everyone, I'm new here and I would like it if you help me with excel formulas.

 

I want from excel to color a cell if these 3 words are not writing on it basic, Moyenne and complete

 

in other words, if I type a word except for basic or Moyenne or complete the cell will be colored with red and the word in the white 

 

please can you help me with this 

here is my email : shevaa262@gmail.com

2 Replies
Highlighted

@abdou_el96 I just joined this community. You can use conditional formatting > highlight cell rules > More rules > Select the preference and set the rules. 

Highlighted

@abdou_el96 

If only one of the excluded words could show up in a cell, select the whole data range, use Conditional Formatting with formula rule =XOR(ISERROR(SEARCH("basic",F2)),ISERROR(SEARCH("moyenne",F2)),ISERROR(SEARCH("complete",F2))) and format red fill with white text

 

However, if the excluded words may show up at the same time in a cell, select whole data range, choose red fill with white text. Then apply Conditional formatting with formula rule =OR(IFERROR(SEARCH("basic",$A2)>0,0),IFERROR(SEARCH("moyenne",$A2)>0,0),IFERROR(SEARCH("complete",$A2)>0,0)) and format no fill with automatic text color

 

If either solution works for your purpose, simply copy cell F2 (solution 1) or A2 (solution 2) in the attached workbook and paste special format to anywhere you want to apply the rule and formatting.