VBA code for formating needed

RickeyS

Board Regular
Joined
Jun 23, 2006
Messages
222
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jun 23, 2006
Messages
222
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

Board Regular
Joined
Jun 23, 2006
Messages
222
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,402
Messages
5,547,748
Members
410,810
Latest member
thepinkbird
Top