Conditional formatting to color rowsbased on cell value

[ezrecall]

How can I color rows based on the cell value of one cell in each row? That
is, if I use conditional formatting to test the value of cells in column P, I
can color cells in that column with conditional formatting, but I want all
cells in each row to have the format of the cell in column P of that row.

 

[mike]

You need to "lock" the cell that determines the condition so that this
doesn't "float" across the other cells in your range, which causes each cell
to make that determination independantly. You do this by ensuring that your
condition is something like:

=$P$1>5

(The '$' symbol is the key that you are missing)

This will force all cells in the row that you have selected for formatting
to only look at P1 instead of their individual values.

For example in excel 2007 you can:

select the cells in your row to recieve the formatting, select conditional
formatting, select 'new rule', select 'use a formula to determine . . . .'.
enter your condition (remember to 'lock' the cell ie. '$P$1>5')
set your fill colour.

You can drag the row up or down to copy the formatting to other rows, or use
copy, paste special, formats.

You can use this with other styles of formats as well where you are allowed
to enter a formula.

I had the same question a few months back . . . took me a while to figure it
out, but its just that one simple thing.


Mike