Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2179622%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2179622%22%20slang%3D%22en-US%22%3E%3CP%3EI%20set%20up%20conditional%20formatting%20on%20a%20date%20field%2C%20column%20D%2C%20highlighting%20this%20week%20and%20next%20week.%26nbsp%3B%20What%20I%20would%20like%20to%20do%20remove%20the%20conditional%20formatting%20from%20rows%20if%20it%20has%20a%20value%20in%20column%20Q%3F%26nbsp%3B%20Can%20this%20be%20done%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2179622%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2179718%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2179718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F984036%22%20target%3D%22_blank%22%3E%40WillBeachHHS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20dates%20in%20column%20D.%20I'll%20assume%20that%20D2%20is%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3ERemove%20the%20current%20conditional%20formatting%20rule(s).%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Usea%20formula%20to%20determine%20wich%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAND(Q2%3D%22%22%2CD2%26gt%3BTODAY()-WEEKDAY(TODAY())%2CD2%26lt%3B%3DTODAY()-WEEKDAY(TODAY())%2B14)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3ESpecify%20the%20desired%20formatting%20in%20the%20Font%20and%20Fill%20tabs.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I set up conditional formatting on a date field, column D, highlighting this week and next week.  What I would like to do remove the conditional formatting from rows if it has a value in column Q?  Can this be done?

4 Replies

@WillBeachHHS 

Select the dates in column D. I'll assume that D2 is the active cell in the selection.

Remove the current conditional formatting rule(s).

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Usea formula to determine wich cells to format'.

Enter the formula

 

=AND(Q2="",D2>TODAY()-WEEKDAY(TODAY()),D2<=TODAY()-WEEKDAY(TODAY())+14)

 

Click Format...

Specify the desired formatting in the Font and Fill tabs.

Click OK, then click OK again.

@Hans Vogelaar Thank you for the help!

 

I have a new one, I think it is countifs?

 

I need to total column R if it says "Feb 21" and Column Q if it say "Yes"

 

I cant seem to get this to total?  Any suggestions?  THANK YOU!

 

@WillBeachHHS 

=COUNTIFS(R2:R1000,DATE(2021,2,21),Q2:Q1000,"Yes")

 

Adjust the ranges if needed.

Thank you! I had to change it a bit but it works! =COUNTIFS(R:R,"Feb 21",Q:Q,"Y")