Conditional formatting rule

Closed
Almunda
Posts
1
Registration date
Saturday June 24, 2017
Status
Member
Last seen
June 24, 2017
- Jun 24, 2017 at 05:53 AM
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
- Jun 26, 2017 at 04:26 PM
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

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Jun 24, 2017 at 08:18 AM
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
0
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
0
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Jun 26, 2017 at 04:26 PM
Almunda, Good evening.

Thanks for the feedback.

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