• 549K Members
• 6,969 Online
• 657K Conversations

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

Highlighted
Occasional Contributor

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

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

Highlighted

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

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

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

That works. Thank you very much.

Highlighted

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

Thank you @PascalKTeam for the suggestion.

Highlighted

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

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.