Conditional Formatting based on Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

horseradish - 28 Nov 2007 19:17 GMT

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.

Reply to this Message

Sandy Mann - 28 Nov 2007 19:28 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> 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
[quoted text clipped - 14 lines]
> in doing this in Excel 2007, but it would be nice to know if it were
> possible in other versions as well.

Reply to this Message

hall.jeff@gmail.com - 28 Nov 2007 22:00 GMT

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)

Reply to this Message

horseradish - 29 Nov 2007 16:04 GMT

> Use a relative reference like:
>
[quoted text clipped - 3 lines]
> 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())))

Reply to this Message