Home

Conditional formatting based on a value not occurring in another range of cells

%3CLINGO-SUB%20id%3D%22lingo-sub-907271%22%20slang%3D%22en-US%22%3EConditional%20formatting%20based%20on%20a%20value%20not%20occurring%20in%20another%20range%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907271%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20many%20different%20ways%20to%20get%20a%20range%20of%20cells%20to%20be%20formatted%20in%20a%20certain%20way%20based%20on%20a%20specific%20value%20not%20occurring%20in%20a%20different%20range%20of%20cells.%20I%20want%20the%20the%20cells%20in%20a%20specific%20range%20to%20look%20one%20way%20if%20the%20value%20%22T%22%20is%20NOT%20in%20a%20range%20of%20cells%20and%20look%20another%20way%20if%20any%20of%20the%20cells%20in%20that%20same%20range%20do%20equal%20%22T.%22%20This%20seems%20pretty%20basic%20but%20I've%20tried%20various%20things%20and%20nothing%20works!%20For%20example%3A%3C%2FP%3E%3CP%3E%3D%24N%2414%3A%24AB%2414%3D%22T%22%3C%2FP%3E%3CP%3Ein%20one%20rule%20and%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%24N%2414%3A%24AB%2414%26lt%3B%26gt%3B%22T%22%3C%2FP%3E%3CP%3Ein%20another%20rule%3C%2FP%3E%3CP%3Ewith%20different%20formatting%20rules%20to%20apply%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-907271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907951%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20a%20value%20not%20occurring%20in%20another%20range%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F197807%22%20target%3D%22_blank%22%3E%40John%20Hendrickson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20maybe%20start%20with%20a%20function%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20516px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F137131i10AA0056EC1A7C69%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-11_09h27_35.png%22%20title%3D%222019-10-11_09h27_35.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908140%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20a%20value%20not%20occurring%20in%20another%20range%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F197807%22%20target%3D%22_blank%22%3E%40John%20Hendrickson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20make%20a%20New%20Rule%20using%20the%20formula%20given%20below...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(%24N%2414%3A%24AB%2414%2C%22T%22)%26gt%3B0%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20formula%20will%20return%20True%20and%20apply%20the%20set%20conditional%20formatting%20if%20a%20'T'%20is%20found%20in%20the%20range%20%24N%2414%3A%24AB%2414%20else%20it%20will%20return%20False.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20to%20set%20the%20conditional%20formatting%20when%20there%20is%20no%20'T'%20in%20the%20range%20%24N%2414%3A%24AB%2414%2C%20you%20may%20use%20the%20formula...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(%24N%2414%3A%24AB%2414%2C%22T%22)%3D0%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908536%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20a%20value%20not%20occurring%20in%20another%20range%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20works.%20Thank%20you%20very%20much.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908538%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20a%20value%20not%20occurring%20in%20another%20range%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908538%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3Bfor%20the%20suggestion.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908601%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20a%20value%20not%20occurring%20in%20another%20range%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F197807%22%20target%3D%22_blank%22%3E%40John%20Hendrickson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20which%20resolved%20your%20question%20as%20a%20Best%20Response%2FAnswer%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
John Hendrickson
Occasional Contributor

I have tried many different ways to get a range of cells to be formatted in a certain way based on a specific value not occurring in a different range of cells. I want the the cells in a specific range to look one way if the value "T" is NOT in a range of cells and look another way if any of the cells in that same range do equal "T." This seems pretty basic but I've tried various things and nothing works! For example:

=$N$14:$AB$14="T"

in one rule and 

=$N$14:$AB$14<>"T"

in another rule

with different formatting rules to apply

 

5 Replies

@John Hendrickson 

You can make a New Rule using the formula given below...

 

=COUNTIF($N$14:$AB$14,"T")>0

 

The above formula will return True and apply the set conditional formatting if a 'T' is found in the range $N$14:$AB$14 else it will return False.

 

If you have to set the conditional formatting when there is no 'T' in the range $N$14:$AB$14, you may use the formula...

 

=COUNTIF($N$14:$AB$14,"T")=0

 

 

@John Hendrickson 

You're welcome! Glad it worked as desired.

 

Please take a minute to accept the post with the proposed solution which resolved your question as a Best Response/Answer to mark your question as Solved.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies