SOLVED

Conditional Formatting Help

%3CLINGO-SUB%20id%3D%22lingo-sub-729995%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729995%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20Im%20trying%20to%20apply%20conditional%20formatting%20to%20Column%20W%20if%20the%20following%3A%3C%2FP%3E%3CP%3EIf%20AF1%3D%22Closed%22%20then%20change%20cell%20colour%20to%20RED%3C%2FP%3E%3CP%3EIF%20H1%3D%22P%22%20and%20N1%26gt%3BO1%20and%20W1%26lt%3B1%20then%20change%20cell%20colour%20to%20Yellow%3C%2FP%3E%3CP%3EIF%20H1%3D%22C%22%20and%20O1%26gt%3BN1%20and%20W1%26lt%3B1%20then%20change%20cell%20colour%20to%20Yellow%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-729995%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-730072%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F240888%22%20target%3D%22_blank%22%3E%40Yehuda%20Ordower%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Select%20all%20of%20Column%20W%3C%2FP%3E%3CP%3E2)%20select%20new%20rule%20in%20the%20conditional%20formatting%20options%20and%20select%20the%20option%20to%20use%20formulas%20to%20format%3C%2FP%3E%3CP%3E3)%20Type%20in%3A%20%3CSTRONG%3E%3DAF1%3D%22Closed%22%2C%26nbsp%3B%3C%2FSTRONG%3Eselect%20the%20fill%20colour%20in%20the%20format%20options%20to%20be%20red%20and%20click%20OK%3C%2FP%3E%3CP%3E4)%20Select%20column%20W%2C%20New%20Rule%20and%20the%20option%20to%20use%20formulas%20to%20format%20again%3C%2FP%3E%3CP%3E5)%20Type%20in%3A%26nbsp%3B%3CSTRONG%3E%3DOR(AND(H1%3D%22P%22%2C%20N1%26gt%3BO1%2C%20W1%26lt%3B1)%2C%20AND(H1%3D%22C%22%2C%20O1%26gt%3BN1%2C%20W1%26lt%3B1))%26nbsp%3B%3C%2FSTRONG%3Eand%20select%20the%20fill%20colour%20in%20the%20format%20options%20to%20be%20Yellow.%20Then%20Click%20OK%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi - Im trying to apply conditional formatting to Column W if the following:

If AF1="Closed" then change cell colour to RED

IF H1="P" and N1>O1 and W1<1 then change cell colour to Yellow

IF H1="C" and O1>N1 and W1<1 then change cell colour to Yellow

 

Thanks.

1 Reply
Highlighted
Solution

@Yehuda Ordower 

 

1) Select all of Column W

2) select new rule in the conditional formatting options and select the option to use formulas to format

3) Type in: =AF1="Closed", select the fill colour in the format options to be red and click OK

4) Select column W, New Rule and the option to use formulas to format again

5) Type in: =OR(AND(H1="P", N1>O1, W1<1), AND(H1="C", O1>N1, W1<1)) and select the fill colour in the format options to be Yellow. Then Click OK