Conditional Formatting with relative cells
 

I have just taken over a spreadsheet that has a lot of pretty complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really be
checking the value of M# and J# based on the row that the cell is in ie on eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative? (I'm
using 2007, but the spreadsheet will be used on machines using earlier
versions)

Thanks

Conditional Formatting with relative cells
 

If you select all the cells in the CF first, then use a relative reference
(no "$" for the row), it should work just fine. Use the active cell's row as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really be
checking the value of M# and J# based on the row that the cell is in ie on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative? (I'm
using 2007, but the spreadsheet will be used on machines using earlier
versions)

Thanks

Conditional Formatting with relative cells
 

Thanks for your swift answer Bob, but have I misunderstood you? CF means
conditional formatting? Because what I did was to select all the cells to
which I wanted to apply the conditional formatting, but then I am stuck
because the formula (that I posted) appears to have relative values in it,
but applying it to the whole range in one go they don't behave as relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H :-)

"Bob Umlas" wrote:

If you select all the cells in the CF first, then use a relative reference
(no "$" for the row), it should work just fine. Use the active cell's row as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really be
checking the value of M# and J# based on the row that the cell is in ie on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative? (I'm
using 2007, but the spreadsheet will be used on machines using earlier
versions)

Thanks

Conditional Formatting with relative cells
 

Helen what is the column or cell are you looking to format?

I have tested it and works.


--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"HelenJ" escreveu:

Thanks for your swift answer Bob, but have I misunderstood you? CF means
conditional formatting? Because what I did was to select all the cells to
which I wanted to apply the conditional formatting, but then I am stuck
because the formula (that I posted) appears to have relative values in it,
but applying it to the whole range in one go they don't behave as relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H :-)

"Bob Umlas" wrote:

If you select all the cells in the CF first, then use a relative reference
(no "$" for the row), it should work just fine. Use the active cell's row as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really be
checking the value of M# and J# based on the row that the cell is in ie on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative? (I'm
using 2007, but the spreadsheet will be used on machines using earlier
versions)

Thanks

Conditional Formatting with relative cells
 

What is the range of cells you want to apply the conditional formatting to?

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FAL SE),FALSE)

That formula can be reduced to:

=AND(M5<"",M5<J5+TIME(0,15,0))


--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for your swift answer Bob, but have I misunderstood you? CF means
conditional formatting? Because what I did was to select all the cells to
which I wanted to apply the conditional formatting, but then I am stuck
because the formula (that I posted) appears to have relative values in it,
but applying it to the whole range in one go they don't behave as
relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H :-)

"Bob Umlas" wrote:

If you select all the cells in the CF first, then use a relative
reference
(no "$" for the row), it should work just fine. Use the active cell's row
as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty
complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really be
checking the value of M# and J# based on the row that the cell is in ie
on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative?
(I'm
using 2007, but the spreadsheet will be used on machines using earlier
versions)

Thanks

Conditional Formatting with relative cells
 

Thanks for the reduced formula, and the use of the TIME function, much
clearer :-).

The conditional formatting for this case, (there a lot of different versions
and I am picking my way through the whole sheet!) is applied to
=$M$5:$M$1940,$M$1942:$M$2289. I suspect this is a mistake and it should be
applied to =$M$5:$M$2289.

So in essence I am looking for conditional formatting that can examine the
cell that it is based on and one, or more, other relative cells to establish
the relevant condition.

Does that make sense?

By experimenting I have found that if I apply the condition to just one cell
and then paste the format to one more cell then it does make the formula
relative, but if I do it to a large range then it effectively becomes
absolute even though the formula isn't. Clearly I would like to avoid copy /
paste format over thousands of individual cells!

Thanks.



"T. Valko" wrote:

What is the range of cells you want to apply the conditional formatting to?

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FAL SE),FALSE)

That formula can be reduced to:

=AND(M5<"",M5<J5+TIME(0,15,0))


--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for your swift answer Bob, but have I misunderstood you? CF means
conditional formatting? Because what I did was to select all the cells to
which I wanted to apply the conditional formatting, but then I am stuck
because the formula (that I posted) appears to have relative values in it,
but applying it to the whole range in one go they don't behave as
relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H :-)

"Bob Umlas" wrote:

If you select all the cells in the CF first, then use a relative
reference
(no "$" for the row), it should work just fine. Use the active cell's row
as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty
complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really be
checking the value of M# and J# based on the row that the cell is in ie
on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative?
(I'm
using 2007, but the spreadsheet will be used on machines using earlier
versions)

Thanks

Conditional Formatting with relative cells
 

Try this:

Select the *entire range of interest* M5:M2289.

You can do this quickly by typing the range into the name box. The name box
is that little "box" immediately above the column A header. Click in the
name box, type the range M5:M2289 then hit Enter.

The range M5:M2289 will be select and cell M5 will be the active cell. The
active cell is the single cell in the selected range that is not shaded. The
formula to be used will be relative to the active cell.

With the range selected...

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format
Enter this formula in the box below:
=AND(M5<"",M5<J5+TIME(0,15,0))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for the reduced formula, and the use of the TIME function, much
clearer :-).

The conditional formatting for this case, (there a lot of different
versions
and I am picking my way through the whole sheet!) is applied to
=$M$5:$M$1940,$M$1942:$M$2289. I suspect this is a mistake and it should
be
applied to =$M$5:$M$2289.

So in essence I am looking for conditional formatting that can examine the
cell that it is based on and one, or more, other relative cells to
establish
the relevant condition.

Does that make sense?

By experimenting I have found that if I apply the condition to just one
cell
and then paste the format to one more cell then it does make the formula
relative, but if I do it to a large range then it effectively becomes
absolute even though the formula isn't. Clearly I would like to avoid
copy /
paste format over thousands of individual cells!

Thanks.



"T. Valko" wrote:

What is the range of cells you want to apply the conditional formatting
to?

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FAL SE),FALSE)

That formula can be reduced to:

=AND(M5<"",M5<J5+TIME(0,15,0))


--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for your swift answer Bob, but have I misunderstood you? CF
means
conditional formatting? Because what I did was to select all the cells
to
which I wanted to apply the conditional formatting, but then I am stuck
because the formula (that I posted) appears to have relative values in
it,
but applying it to the whole range in one go they don't behave as
relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H :-)

"Bob Umlas" wrote:

If you select all the cells in the CF first, then use a relative
reference
(no "$" for the row), it should work just fine. Use the active cell's
row
as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty
complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really
be
checking the value of M# and J# based on the row that the cell is in
ie
on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative?
(I'm
using 2007, but the spreadsheet will be used on machines using
earlier
versions)

Thanks

Conditional Formatting with relative cells
 

Many thanks for all your help. I've just realised, pretty much what you have
said, that the formula is relative to the first cell in the range - and of
course it doesn't appear to change.

My problem was that the formatting on this spreadsheet is so complicated and
several of the formulae are even more convoluted (7 or eight multiple levels
of interlinked nested ifs!) that I leapt to the conclusion it was the
conditional formatting that wasn't working.

Well I have now learnt a lot (and I feel much happier to sort out the rest
of this spreadsheet).

So thanks everyone for your help.

H

"T. Valko" wrote:

Try this:

Select the *entire range of interest* M5:M2289.

You can do this quickly by typing the range into the name box. The name box
is that little "box" immediately above the column A header. Click in the
name box, type the range M5:M2289 then hit Enter.

The range M5:M2289 will be select and cell M5 will be the active cell. The
active cell is the single cell in the selected range that is not shaded. The
formula to be used will be relative to the active cell.

With the range selected...

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format
Enter this formula in the box below:
=AND(M5<"",M5<J5+TIME(0,15,0))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for the reduced formula, and the use of the TIME function, much
clearer :-).

The conditional formatting for this case, (there a lot of different
versions
and I am picking my way through the whole sheet!) is applied to
=$M$5:$M$1940,$M$1942:$M$2289. I suspect this is a mistake and it should
be
applied to =$M$5:$M$2289.

So in essence I am looking for conditional formatting that can examine the
cell that it is based on and one, or more, other relative cells to
establish
the relevant condition.

Does that make sense?

By experimenting I have found that if I apply the condition to just one
cell
and then paste the format to one more cell then it does make the formula
relative, but if I do it to a large range then it effectively becomes
absolute even though the formula isn't. Clearly I would like to avoid
copy /
paste format over thousands of individual cells!

Thanks.



"T. Valko" wrote:

What is the range of cells you want to apply the conditional formatting
to?

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FAL SE),FALSE)

That formula can be reduced to:

=AND(M5<"",M5<J5+TIME(0,15,0))


--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for your swift answer Bob, but have I misunderstood you? CF
means
conditional formatting? Because what I did was to select all the cells
to
which I wanted to apply the conditional formatting, but then I am stuck
because the formula (that I posted) appears to have relative values in
it,
but applying it to the whole range in one go they don't behave as
relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H :-)

"Bob Umlas" wrote:

If you select all the cells in the CF first, then use a relative
reference
(no "$" for the row), it should work just fine. Use the active cell's
row
as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty
complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should really
be
checking the value of M# and J# based on the row that the cell is in
ie
on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae relative?
(I'm
using 2007, but the spreadsheet will be used on machines using
earlier
versions)

Thanks

Conditional Formatting with relative cells
 

Good luck! Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Many thanks for all your help. I've just realised, pretty much what you
have
said, that the formula is relative to the first cell in the range - and of
course it doesn't appear to change.

My problem was that the formatting on this spreadsheet is so complicated
and
several of the formulae are even more convoluted (7 or eight multiple
levels
of interlinked nested ifs!) that I leapt to the conclusion it was the
conditional formatting that wasn't working.

Well I have now learnt a lot (and I feel much happier to sort out the rest
of this spreadsheet).

So thanks everyone for your help.

H

"T. Valko" wrote:

Try this:

Select the *entire range of interest* M5:M2289.

You can do this quickly by typing the range into the name box. The name
box
is that little "box" immediately above the column A header. Click in the
name box, type the range M5:M2289 then hit Enter.

The range M5:M2289 will be select and cell M5 will be the active cell.
The
active cell is the single cell in the selected range that is not shaded.
The
formula to be used will be relative to the active cell.

With the range selected...

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format
Enter this formula in the box below:
=AND(M5<"",M5<J5+TIME(0,15,0))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for the reduced formula, and the use of the TIME function, much
clearer :-).

The conditional formatting for this case, (there a lot of different
versions
and I am picking my way through the whole sheet!) is applied to
=$M$5:$M$1940,$M$1942:$M$2289. I suspect this is a mistake and it
should
be
applied to =$M$5:$M$2289.

So in essence I am looking for conditional formatting that can examine
the
cell that it is based on and one, or more, other relative cells to
establish
the relevant condition.

Does that make sense?

By experimenting I have found that if I apply the condition to just one
cell
and then paste the format to one more cell then it does make the
formula
relative, but if I do it to a large range then it effectively becomes
absolute even though the formula isn't. Clearly I would like to avoid
copy /
paste format over thousands of individual cells!

Thanks.



"T. Valko" wrote:

What is the range of cells you want to apply the conditional
formatting
to?

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FAL SE),FALSE)

That formula can be reduced to:

=AND(M5<"",M5<J5+TIME(0,15,0))


--
Biff
Microsoft Excel MVP


"HelenJ" wrote in message
...
Thanks for your swift answer Bob, but have I misunderstood you? CF
means
conditional formatting? Because what I did was to select all the
cells
to
which I wanted to apply the conditional formatting, but then I am
stuck
because the formula (that I posted) appears to have relative values
in
it,
but applying it to the whole range in one go they don't behave as
relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H :-)

"Bob Umlas" wrote:

If you select all the cells in the CF first, then use a relative
reference
(no "$" for the row), it should work just fine. Use the active
cell's
row
as
the sample to apply to the whole range.

"HelenJ" wrote in message
...
I have just taken over a spreadsheet that has a lot of pretty
complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<"",IF(M5<(J5+0.0104166666666667),TRUE,FALS E),FALSE)

this is applied to a whole column, the trouble is it should
really
be
checking the value of M# and J# based on the row that the cell is
in
ie
on
eg
row 99 it should be:
=IF(M99<"",IF(M99<(J99+0.0104166666666667),TRUE,F ALSE),FALSE).

Is there any way of making conditional formatting formulae
relative?
(I'm
using 2007, but the spreadsheet will be used on machines using
earlier
versions)

Thanks