Conditional formatting in Excel 2007

[Guest]

This was easy to do in earlier versions of Excel but I cannot find how to do
it in Excel 2007.

I have around 50 columns of data with several hundred values in each column.
At the bottom of each column (always in the 524th row) I have a "threshold
value". The threshold value is different for each column. I want to
highlight values in the column that are above the threshold value for that
column.

This is easy to do for one column at a time, but using the format paintbrush
does not copy the appropriate formula, even if I take care to make the
comparison cell partially relative (e.g., A$524 rather than $A$524).

Can anybody help?

 

[Roger Govier]

Hi Mike

Works just fine for me with XL2007, either by doing a single column and
using format painter, or selecting all columns first and Home
tab>Conditional Formatting>New rule>Use formula> =A1<A$524

 

[Guest]

Hi Mike

Works just fine for me with XL2007, either by doing a single column and
using format painter, or selecting all columns first and Home
tab>Conditional Formatting>New rule>Use formula> =A1<A$524

Thanks, Roger, for taking a stab at this.

I can follow your example and get it to work OK, but what formula would you
use if the top three rows of the table have titles in them and you don't want
to apply the conditional format to them?

Mike

 

[Roger Govier]

Hi Mike

Personally, I would miss out those rows from the range to be formatted
and starting with rows 4 make the formula
=A4<A$524

However, if you wanted to do it purely by formula, then
=AND(ROW()>3,A1<$A524)