Hi, thanks a million Bernie.

My conditional formatting applies to a number of columns too- different
dates.. Your formula applies the format to the whole row. So what if I only
wanted to apply the formatting to the cell in that row which wasn't empty.
(it will contain the same job number which was used as the lookup value- i.e.
A5)
I'm running into a similar problem where the cells I want to reference in
the conditional formatting formula are the cells that the format actually
applies to.
e.g: I wish I could do this in my formula:

=AND(VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1", NOT(ISBLANK("cells in row
9 to which formula applies")))


my spreadsheet looks much like ms project with jobs on the left, dates along
the top and coloured cells where the dates and jobs correspond.

Cheers for the help,
Tyron

"Bernie Deitrick" wrote:

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)