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
Highlighted
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
Highlighted

@John Hendrickson 

 

Hi, maybe start with a function like this:

2019-10-11_09h27_35.png

Highlighted

@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

 

 

Highlighted
Highlighted
Highlighted

@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.