Conditional format if not on list

P

pkeegs

I would like to apply a conditional format into a cell, so that when I type a
name into that cell, if it doesn't exist on a separate list of names, the
condition will apply. Is it possible?
 
J

Joerg Mochikun

Yes, possible.

As condition use a formula:
=ISERROR(MATCH(A1,listrange,0))

Above example assumes that you have a named range 'listrange' that holds
your separate list of names (of course you could also use something like
$B1$:$B$10) and that your cell containing the conditional format is A1. This
will format all cell entries in A1 that are NOT in the specified list.

Cheers,
Joerg Mochikun
 
M

Muhammed Rafeek M

Yes.
Select cell where u want apply conditional format and select Formula from
conditional format box and type below mentioned formula

=ISERROR(VLOOKUP(B1,$A$1:$A$100,1,0))

and set the format which format u want.

in this formula u need to change cell range
in that formula, Cell B1 is which cell u selected, and $A$1:$A$100 range is
list of names ( You can use Name list also).

Please do rate.

Regards
 
P

pkeegs

Thanks guys (Joerg & Muhammed) both conditional formulas work well

Regards - pkeegs
 
Top