SOLVED

Need help with conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-3373626%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3373626%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20background%20I%20am%20trying%20to%20create%20a%20document%20to%20make%20it%20easy%20for%20employees%20to%20sign%20up%20for%20%E2%80%9Csplit%20shifts%E2%80%9D.%26nbsp%3B%3CBR%20%2F%3EMy%20desire%20is%20that%20when%20two%20employees%20put%20their%20names%20in%20Column%20B%20and%20Column%20C%20that%20those%20columns%20automatically%20turn%20orange%20so%20that%20managers%20can%20quickly%20see%20who%20they%20need%20to%20approve.%20Once%20they%20sign%20their%20initials%20for%20approval%20in%20Column%20D%20I%20would%20like%20for%20Columns%20B-D%20to%20change%20to%20green%20so%20we%20all%20know%20the%20shift%20has%20been%20approved.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20have%20tried%20using%20IFTEXT%20conditional%20formatting%20but%20have%20been%20unsuccessful.%20Can%20I%20get%20advice%20on%20how%20to%20format%20so%20that%20anytime%20B%20and%20C%20columns%20have%20text%20entered%20they%20turn%20orange%20and%20once%20D%20is%20also%20filled%20in%20they%20all%20turn%20green%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22C6144ED1-1788-4F14-80C6-9898B2D1A99C.png%22%20style%3D%22width%3A%201369px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371590i9560C75B11407AAA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22C6144ED1-1788-4F14-80C6-9898B2D1A99C.png%22%20alt%3D%22C6144ED1-1788-4F14-80C6-9898B2D1A99C.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3373626%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-3373935%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3373935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389951%22%20target%3D%22_blank%22%3E%40Sara1713%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuppose%20you%20want%20to%20apply%20the%20conditional%20formatting%20to%20the%20range%20B4%3AC20%20then%20select%20the%20range%20B4%3AC20%20and%20make%20the%20new%20conditional%20formatting%20rules%20using%20the%20formulas%20given%20below...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Orange%3A%3C%2FP%3E%3CP%3E%3DAND(COUNTA(%24B4%3A%24C4)%3D2%2CD4%3D%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Green%3A%3C%2FP%3E%3CP%3E%3DAND(COUNTA(%24B4%3A%24C4)%3D2%2C%24D4%26lt%3B%26gt%3B%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375362%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375362%22%20slang%3D%22en-US%22%3EThank%20you%20that%20worked%20perfectly!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375612%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375612%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389951%22%20target%3D%22_blank%22%3E%40Sara1713%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

As background I am trying to create a document to make it easy for employees to sign up for “split shifts”. 
My desire is that when two employees put their names in Column B and Column C that those columns automatically turn orange so that managers can quickly see who they need to approve. Once they sign their initials for approval in Column D I would like for Columns B-D to change to green so we all know the shift has been approved. 

I have tried using IFTEXT conditional formatting but have been unsuccessful. Can I get advice on how to format so that anytime B and C columns have text entered they turn orange and once D is also filled in they all turn green? 

C6144ED1-1788-4F14-80C6-9898B2D1A99C.png

3 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@Sara1713 

 

Suppose you want to apply the conditional formatting to the range B4:C20 then select the range B4:C20 and make the new conditional formatting rules using the formulas given below...

 

For Orange:

=AND(COUNTA($B4:$C4)=2,D4="")

 

For Green:

=AND(COUNTA($B4:$C4)=2,$D4<>"")

Thank you that worked perfectly!!

You're welcome @Sara1713! Glad it worked as desired.