Conditional formatting rule

Closed
Report
Posts
1
Registration date
Saturday June 24, 2017
Status
Member
Last seen
June 24, 2017
-
Posts
1947
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 11, 2021
-
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"?

2 replies

Posts
1947
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 11, 2021
147
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
Obrigado Mazzaropi! Your suggestion was really helpful. It took me a little while to get it to work but got there in the end. Very grateful, Almunda
Posts
1947
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 11, 2021
147
Almunda, Good evening.

Thanks for the feedback.

Glad to have helped you in your work.
--
Belo Horizonte, Brasil.
Marcílio Lobão