Conditional Formatting, multiple conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2839076%22%20slang%3D%22en-US%22%3EConditional%20Formatting%2C%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2839076%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%20I%20need%20a%20little%20help%20with%20conditional%20formatting%2C%20everything%20I've%20tried%20has%20not%20worked.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20schedule%20output%20excel%20sheet%2C%20were%20the%20scheduling%20information%20is%20entered%20on%20one%20tab%20and%20it%20outputs%20a%20nice%20looking%20schedule%20on%20a%20different%20tab.%20I%20used%20a%20template%20to%20get%20started%20and%20have%20been%20able%20to%20adjust%20everything%20to%20make%20it%20work%20except%20for%20the%20conditional%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20template%20used%20an%20index%2Fmatch%20formula%20to%20pull%20over%20values%2C%20and%20the%20conditional%20formatting%20makes%20it%20so%20that%20sometimes%20the%20values%20show%20and%20other%20times%20they%20are%20hidden%20(same%20background%2Ffont%20color).%20I%20love%20everything%20except%20I%20would%20like%20the%20%22shown%20values%22%20to%20be%20different%20colors%20based%20on%20the%20content%20of%20cell%2C%20I'm%20using%20%22FIT%22%20for%20this%20example.%20(see%20attached%20screenshots%20for%20schedule%20and%20conditional%20formatting.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20adding%20to%20the%20%22and%22%20function%20(this%20is%20the%20existing%20formula%20%3DAND(H5%26lt%3B%26gt%3B0%2C%24AF5%3CCAL_ENDTIME%3E%3CUL%3E%3CLI%3E%3DAND(AH5%3D%22FIT%22%2CH5%26lt%3B%26gt%3B0%2C%24AF5%3CCAL_ENDTIME%3E%3C%2FCAL_ENDTIME%3E%3C%2FLI%3E%3CLI%3E%3DAND(AH5%3D%22*FIT*%22%2CH5%26lt%3B%26gt%3B0%2C%24AF5%3CCAL_ENDTIME%3E%3C%2FCAL_ENDTIME%3E%3C%2FLI%3E%3CLI%3E%3DAND(ISNUMBER(FIND(%22FIT%22%2CAH5))%2CH5%26lt%3B%26gt%3B0%2C%24AF5%3CCAL_ENDTIME%3E%3C%2FCAL_ENDTIME%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FCAL_ENDTIME%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20things%20I've%20added%20work%20on%20their%20own%2C%20but%20do%20not%20work%20when%20combined%20in%20any%20of%20these%20ways%20with%20the%20above%20format.%20Any%20advice%20would%20be%20very%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2839076%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-2839376%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%2C%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2839376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1183473%22%20target%3D%22_blank%22%3E%40gabepatten%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20need%20to%20wrap%20your%20ANDs%20in%20an%20IF%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(AH5%3D%22FIT%22%2CAND(H5%26lt%3B%26gt%3B0%2C%24AF5%3CCAL_ENDTIME%3E%3C%2FCAL_ENDTIME%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2839730%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%2C%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2839730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1181078%22%20target%3D%22_blank%22%3E%40edawcj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20suggestion!%20I%20tried%20it%20out%2C%20and%20it's%20not%20working%2C%20it%20removes%20the%20formatting.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi everyone! I need a little help with conditional formatting, everything I've tried has not worked. 

 

I am working on a schedule output excel sheet, were the scheduling information is entered on one tab and it outputs a nice looking schedule on a different tab. I used a template to get started and have been able to adjust everything to make it work except for the conditional formatting.

 

The template used an index/match formula to pull over values, and the conditional formatting makes it so that sometimes the values show and other times they are hidden (same background/font color). I love everything except I would like the "shown values" to be different colors based on the content of cell, I'm using "FIT" for this example. (see attached screenshots for schedule and conditional formatting.)

 

I have tried adding to the "and" function (this is the existing formula =AND(H5<>0,$AF5<Cal_Endtime)) in the formatting to include all of the following, but it hasn't worked:

  • =AND(AH5="FIT",H5<>0,$AF5<Cal_Endtime)
  • =AND(AH5="*FIT*",H5<>0,$AF5<Cal_Endtime)
  • =AND(ISNUMBER(FIND("FIT",AH5)),H5<>0,$AF5<Cal_Endtime)

 

The things I've added work on their own, but do not work when combined in any of these ways with the above format. Any advice would be very much appreciated!

 

 

3 Replies

@gabepatten 

You need to wrap your ANDs in an IF like this:

=IF(AND(AH5="FIT",AND(H5<>0,$AF5<Cal_Endtime)),TRUE,FALSE)

 

@edawcj 

 

Thanks for the suggestion! I tried it out, and it's not working, it removes the formatting. 

@edawcj updated screenshot.. where did I go wrong?