Changing cell format if it contains text from a list

[Gabylone]

Hi everyone,

I have a rather complicated thing to do ( at least I think )

I have a list of words on the first sheet of my file. ( it’s at least 20 rows long )

example :

• Cow
• Bird
• Shovel

---

On the other sheets of the file, I want the following behavior :

I would like that if my cell doesn’t contain any of the words of my list ( cow, bird or shovel ), it turns red ( change the style )

I’ve tried conditionnal formatting. But it’s very long and hard to change, and the software starts crashing after too many sheets.

To you have any solutions,

Thanks in advance,

Gabriel.

[erAck]

Assuming your list of words is in cells A1:A20 on Sheet1 then

1. define a named range, for example with the name wordlist, to that range of $Sheet1.$A$1:$A$20, ensure all reference parts are absolute, i.e. prefixed with $
2. position the cell cursor on a cell where the conditional format is to be applied, for example on Sheet2 on B3
3. menu Format - Conditional Formatting… - Condition
4. select “Formula is”
5. enter ISERROR(VLOOKUP(B3,wordlist,1,0)) as formula. Note that the B3 used here matches the B3 the cell cursor is on
6. select style to be applied
7. click OK
8. copy & paste the cell to a destination range as needed; to copy only the format if a cell already has a value use PasteSpecial (Shift+Ctrl+V) and select only Formats

You can then add words to the list on Sheet1 and if it extends beyond A20 simply redefine the wordlist named range.

[PYS]

Hi erAck - We responded at the same time. Your answer is more detailed on conditional formatting but I nevertheless leave my answer for the “validity” suggestion

Regards

[Gabylone]

I’ll check out these solutions and validate tonight ! Thanks alot !

[PYS]

Hi

You can use Data▸Validity▸Cell Range on the other sheets to limit input to $Sheet1.$A$1:$A$3

With conditional formatting you can use a formula like:

ISNA(MATCH(A1;Sheet1.$A$1:$A$3;0))

Regards

[Gabylone]

Merci Pierre-Yves je regarderai ce soir. Je te dis si ça marche.

[mariosv]

With the actual Conditional format, we can have defined a condition for several range at once. So we can avoid copy the condition to have it applied, it does easier modify a condition.