Conditional Cell Formatting
 

Excel 2007

I have an 8 x 8 grid in which I want to flag all cells that are not evenly
divisible by .5. I can do this for a single cell with the following formula
as the conditional formatting criteria:

=MOD($R$8,0.5)0

The obvious drawback is that this formula refers to the specific cell, so to
set it up in an 8 x 8 grid would require 64 separate conditional formatting
rules. There's got to be a better way.

Is it possible to create one conditional formatting rule which uses a
formula for the conditional formatting criteria, can be applied to a range,
and acts upon each cell in the range separately and not on the range as a
whole?

--Tom

Conditional Cell Formatting
 

Sure - select the 8x8 grid, use the active cell as the formula, but have it
be relative (no "$")
=MOD(R8,.5)0
if R8 is the ACTIVE cell.

"Thomas M." wrote in message
...
Excel 2007

I have an 8 x 8 grid in which I want to flag all cells that are not evenly
divisible by .5. I can do this for a single cell with the following
formula as the conditional formatting criteria:

=MOD($R$8,0.5)0

The obvious drawback is that this formula refers to the specific cell, so
to set it up in an 8 x 8 grid would require 64 separate conditional
formatting rules. There's got to be a better way.

Is it possible to create one conditional formatting rule which uses a
formula for the conditional formatting criteria, can be applied to a
range, and acts upon each cell in the range separately and not on the
range as a whole?

--Tom

Conditional Cell Formatting
 

Is it possible to create one conditional formatting
rule which uses a formula for the conditional formatting criteria

Yes, use relative references

Let's assume your range is A1:H8

Select *the entire range* of cells you want to format starting from cell A1.
Cell A1 will be the active cell. The active cell is the one cell in the
selected range that *is not* shaded.

The formula you enter will be *relative* to the active cell:

=MOD(A1,0.5)0

The cell reference will automatically adjust for the other cells.


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have an 8 x 8 grid in which I want to flag all cells that are not evenly
divisible by .5. I can do this for a single cell with the following
formula as the conditional formatting criteria:

=MOD($R$8,0.5)0

The obvious drawback is that this formula refers to the specific cell, so
to set it up in an 8 x 8 grid would require 64 separate conditional
formatting rules. There's got to be a better way.

Is it possible to create one conditional formatting rule which uses a
formula for the conditional formatting criteria, can be applied to a
range, and acts upon each cell in the range separately and not on the
range as a whole?

--Tom

Conditional Cell Formatting
 

I have mixed feelings about that answer. On the one hand, I am glad that it
works and my problem is solved. On the other hand, I'm embarrassed that *I*
didn't think of that! ;-)

Seems obvious in retrospect! Thanks for the help.

--Tom

"Bob Umlas" wrote in message
...
Sure - select the 8x8 grid, use the active cell as the formula, but have
it be relative (no "$")
=MOD(R8,.5)0
if R8 is the ACTIVE cell.

"Thomas M." wrote in message
...
Excel 2007

I have an 8 x 8 grid in which I want to flag all cells that are not
evenly divisible by .5. I can do this for a single cell with the
following formula as the conditional formatting criteria:

=MOD($R$8,0.5)0

The obvious drawback is that this formula refers to the specific cell, so
to set it up in an 8 x 8 grid would require 64 separate conditional
formatting rules. There's got to be a better way.

Is it possible to create one conditional formatting rule which uses a
formula for the conditional formatting criteria, can be applied to a
range, and acts upon each cell in the range separately and not on the
range as a whole?

--Tom

Conditional Cell Formatting
 

Thanks for the reply. As in my reply to Bob, I must confess to being a bit
embarrassed that I didn't think of that. It seems so obvious in retrospect.

Thanks for the help.

--Tom

"T. Valko" wrote in message
...
Is it possible to create one conditional formatting
rule which uses a formula for the conditional formatting criteria

Yes, use relative references

Let's assume your range is A1:H8

Select *the entire range* of cells you want to format starting from cell
A1. Cell A1 will be the active cell. The active cell is the one cell in
the selected range that *is not* shaded.

The formula you enter will be *relative* to the active cell:

=MOD(A1,0.5)0

The cell reference will automatically adjust for the other cells.


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have an 8 x 8 grid in which I want to flag all cells that are not
evenly divisible by .5. I can do this for a single cell with the
following formula as the conditional formatting criteria:

=MOD($R$8,0.5)0

The obvious drawback is that this formula refers to the specific cell, so
to set it up in an 8 x 8 grid would require 64 separate conditional
formatting rules. There's got to be a better way.

Is it possible to create one conditional formatting rule which uses a
formula for the conditional formatting criteria, can be applied to a
range, and acts upon each cell in the range separately and not on the
range as a whole?

--Tom

Conditional Cell Formatting
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Thanks for the reply. As in my reply to Bob, I must confess to being a
bit embarrassed that I didn't think of that. It seems so obvious in
retrospect.

Thanks for the help.

--Tom

"T. Valko" wrote in message
...
Is it possible to create one conditional formatting
rule which uses a formula for the conditional formatting criteria

Yes, use relative references

Let's assume your range is A1:H8

Select *the entire range* of cells you want to format starting from cell
A1. Cell A1 will be the active cell. The active cell is the one cell in
the selected range that *is not* shaded.

The formula you enter will be *relative* to the active cell:

=MOD(A1,0.5)0

The cell reference will automatically adjust for the other cells.


--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have an 8 x 8 grid in which I want to flag all cells that are not
evenly divisible by .5. I can do this for a single cell with the
following formula as the conditional formatting criteria:

=MOD($R$8,0.5)0

The obvious drawback is that this formula refers to the specific cell,
so to set it up in an 8 x 8 grid would require 64 separate conditional
formatting rules. There's got to be a better way.

Is it possible to create one conditional formatting rule which uses a
formula for the conditional formatting criteria, can be applied to a
range, and acts upon each cell in the range separately and not on the
range as a whole?

--Tom