You need to write a formula that returns either TRUE or FALSE, so something like this would work:

=VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1"
and set the fill to red...

You, of course, would need either two or three conditions - if you have only three conditions, the
default formatting can take care of one condition, and the CF can take care of the other two.

HTH,
Bernie
MS Excel MVP


"tyronki" wrote in message
...
Hi,

I'm trying to use conditional formatting formulas to change the fill colour
of a large table of cells. The table is automatically populated with job
numbers, of which there are many. There are only 3 job types and on the same
sheet there is a separate table with a column for "job type" and a column for
"job number". I want to use a lookup function in the large table to find the
job type and then change the cell colour to suite the job type.
I've tried using formulas for conditional formating but the formulas limit
you from using certain references. i.e. I want the format of each individual
cell in a range to change according to that individual cell's own lookup
function. But all i can do is change the format of the whole range of cells
according to one cell's lookup function.

it would be so easy if you could change a cell's formatting using code in an
"if" statement.
i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFill Colour=red)