Ask Your Question

Changing cell format if it contains text from a list.

asked 2016-12-14 15:36:17 +0100

Gabylone gravatar image

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,


edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-12-14 16:45:49 +0100

pierre-yves samyn gravatar image


You can use DataValidityCell Range on the other sheets to limit input to $Sheet1.$A$1:$A$3

With conditional formatting you can use a formula like:



edit flag offensive delete link more


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

Gabylone gravatar imageGabylone ( 2016-12-15 16:47:22 +0100 )edit

answered 2016-12-14 17:38:08 +0100

m.a.riosv gravatar image

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.

image description

edit flag offensive delete link more

answered 2016-12-14 16:42:05 +0100

erAck gravatar image

updated 2016-12-14 16:42:51 +0100

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.

edit flag offensive delete link more


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


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-12-14 16:49:33 +0100 )edit

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

Gabylone gravatar imageGabylone ( 2016-12-15 16:47:59 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-12-14 15:36:17 +0100

Seen: 326 times

Last updated: Dec 14 '16