Don't copy the formula down the list!

Select the range A2:A7. A2 should be the active cell. The active cell is
clear while the rest of the cells in the selected range have a "blueish"
color.

Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(semi,A2)=0
Click the Format button
Select the style(s) desired
OK out

As long as the defined name "semi" has absolute references the CF formula
should work. (unless there might be unseen characters like leading/trailing
spaces in some cells)

Biff

"OdAwG" wrote in message
...
That doesn't seem to work for all, when I copied your formula down the
list, it show FALSE for alot of names that appear in the SEMI range



"Dave Peterson" wrote in message
...
How about:

formula is:
=countif(semi,a1)=0

=vlookup() only looks for a match in the first column of the lookup
range.



OdAwG wrote:

Hello All Excel Guru's:

I have another quick question that I need assistance with that pertains
or
deals with conditional format.

I have a list of names in one column and I need to search another list
(range) to see if that name DOES NOT EXIST in that list or range. If
it
DOES NOT Exist, then to change the color of the cell. Listed below is
the
my example:

A B C D E F
1 Names Semi
2 Hulk Hulk Storm Spidey Batman
3 Storm Storm Batman Ironman Spidey
4 Spidey IronMan Phoenix Batman Hulk
5 BatMan Spidey Batman Storm Hulk
6 SuperMan IronMan Hulk Spidey Phoenix
7 IronMan IronMan BatMan Hulk Storm
8 CatWm
9 Phoenix
10 Kitty

A2:A10 is a range called Names
C2:F7 is a range called SEMI

In the above example, the following three (3) names should be colored
out
(CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the
SEMI
range.

=vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think
my
formula is correct and also, do I put this in the conditional format so
that
I can color out the cell.

I'm not very strong in using VLOOKUP, any and all help is greatly
appreciated.

Argus

--

Dave Peterson