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"?
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
