VBA code for formating needed

[RickeyS]

My problem:
I have a range of cells (C16:C43) on sheet (week3&4).
These cells contain text.
I need code to look up and match text on sheet (employees)(cells B5:B43) and when it finds a match if column D is not blank do nothing but if it is blank turn cell on sheet (week3&4) red.

I tried to do this with conditional formatting but it dosn't allow you to do something from another sheet.

Thanks
Rick

 

[Andrew Poulsom]

You can't use a CF formula like this:

=ISNUMBER(MATCH(C16,Employees!B5:B43,FALSE))

but if you name Employees!B5:B43 say List with Insert|Name|Define, you can use:

=ISNUMBER(MATCH(C16,List,FALSE))

 

[RickeyS]

I get error:
"You may not use references to other work sheets or workbooks for conditional formatting criteria"
when I try to use formula in conditional formatting.
So do I need to do this in VBA code? or is there some other way?

Thanks
Rick

 

[RickeyS]

Sorry, I see what your saying now, I give that a try.
Thanks
Rick

 

[BruceyBonu$]

=IF(ISBLANK(E16),ISNUMBER(MATCH(D16,list,FALSE)),FALSE)

 

[RickeyS]

Thanks for help I got it to work with this formula,
=IF(ISBLANK(VLOOKUP(C16,employeelist,3,FALSE)),TRUE,FALSE)
not sure if this is the best way or not but it works when used in conditional formatting.

Thanks again,
Rick