Conditional formatting ruleClosed

Question

I was wondering if anyone could help me to set up a conditional formatting rule for a cell that may contain any text that has not already been specified in a drop down list? For example, if I choose "Apples", "Oranges" or "Pears" the cell changes colour depending on conditional formatting rules already specified BUT HOW could I get the cell to turn consistently to one other colour if I typed in anything other than those fruits mentioned above? In other words, what formula should I use if I wanted the cell to turn Purple if I typed in "Grapes" or "Kiwis"?

Mazzaropi · Answer

Almunda, Good morning.

Your explanation is a bit confusing to me.

I'll give you a hint about what I understood that is your question.
If not, please explain it in a more explicit way.

Suppose:

List of Items --> A1:A6 --> Range named = FRUITS
A1 --> APPLES
A2 --> ORANGES
A3 --> PEARS
A4 --> AVOCADOS
A5 --> CHERRIES
A6 --> PEACHS

Cell to be checked --> D1

1. Select the D1 cell (the cell you want to change the colour)
2. Conditional Formatting ----> New Rule ----> Use a formula to determine which cells to format.
3. Format values where this format is true ----> 
=COUNTIF(FRUITS, D1)=0
4. Select Fill Colour that you want.
OK

Please, tell us if it worked for you.
I hope it helps.

Have a nice day! 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

Mazzaropi · Answer

Almunda, Good evening. 

Thanks for the feedback.

Glad to have helped you in your work. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

Conditional formatting rule

2 replies

Subscribe To Our Newsletter!