conditional formatting many rows

[javablood]

I have a table that has unique identifiers (IDs) in the columns and chemicals
in the rows. Data values fill the rest of the table. There are 5 such sets in
the worksheet. One column in each set contains a value to which I compare
and format each cell according to whether it is greater than or less than
that value. See below for example.If the value is N/A no formatting is
necessary (that is the easy part). I use conditional formatting but it
appears that the formatting and rules from one row gets applied to others.

For example, if the cell contains U no formatting, stop; if greater than 5
format. But in another row if greater than 100 format. Yet, the greater than
5 format is interfering with the greater than 100 format and because of the
'multiple sets' I have and the order of compoounds, I really cannot rearrange
everything to fit in the order of 2, 5, 7, 70, etc.

If I explained this correctly, should I be able to do what I want with CF?
What am I doing wrong?

TIA

MW-16 MW-47
Compound Limits 06/20/09 06/20/09
Methylene Chloride 5 1U 1U
trans-1,2-Dichloro 100 6.8 1U
cis-1,2-Dichloro 70 39 1U
1,1-Dichloroethene 7 2U 1U
1,1-Dichloroethane N/A 110 1U
Chloroform N/A 1.6 1U
1,2-Dichloroethane 5 2U 1U

 

[Luke M]

Assuming your data is in A, with "Methylene Chloride" being on row 3

Select cell C3
Formula is:
=AND($B3<>"N/A",ISNUMBER(C3),C3>$B3)

Copy format to other cells as needed.
Note the placement of the absolute reference symbols. Keeps reference lock
on column B, but allows it to drift between rows. Also, if your "N/A" is
actually generated by a formula, you'll need to replace "N/A" with NA() (but
it sounds like you already got that part figured out).

 

[javablood]

thanks
--
javablood

Assuming your data is in A, with "Methylene Chloride" being on row 3

Select cell C3
Formula is:
=AND($B3<>"N/A",ISNUMBER(C3),C3>$B3)

Copy format to other cells as needed.
Note the placement of the absolute reference symbols. Keeps reference lock
on column B, but allows it to drift between rows. Also, if your "N/A" is
actually generated by a formula, you'll need to replace "N/A" with NA() (but
it sounds like you already got that part figured out).
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*