Conditional Formatting based on Formula

[horseradish]

The built-in rules for conditional formatting (greater than value, less
than value, etc) work on the cell for which the CF is applied WITHOUT
having to specify the cell.

The examples for advanced rules using a formula require the formulas to
respect their arguments. For instance, I'd like to have a CF that
highlights numbers, so I'd rely on "ISNUMBER()" to get a true/false
value that triggers the format. However, ISNUMBER() requires an
argument, and I have been able to enter the cell's address as an
argument to get it to work: apply CF to $A$1 by entering "=ISNUMBER($A$1)".

This is fine for single cells, but applying the rule to a bunch of cells
seems impossible unless there's a "self" or "this cell" wildcard somewhere.

Does anyone know how to do this sort of thing? I'm specifically
interested in doing this in Excel 2007, but it would be nice to know if
it were possible in other versions as well.

 

[Sandy Mann]

Use a relative reference like:

=ISNUMBER(A1)


Conditional formatting loves entering absolute references when you click on
a cell, change it to Relative by highlighting the reference and pressing the
finction key F4 until it shows the way you want.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[hall.jeff]

This is true of named ranges... In fact, we often use a named range
here at the office called "thiscell"

just goto A1 and enter thiscell as =a1 (no $'s)... that way you can
always reference your current location (we do alot of offset functions
that we want to offset from the current cell... this makes reading it
easier when we're sometimes offsetting from the current cell and
sometimes offsetting from a specific other cell)

 

[horseradish]

Use a relative reference like:

=ISNUMBER(A1)


Conditional formatting loves entering absolute references when you click on
a cell, change it to Relative by highlighting the reference and pressing the
finction key F4 until it shows the way you want.

Much better than the workaround I came up with:
=ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN())))