How do I do Conditonal Formatting with formula progression

[Guest]

I am using Excel 2003

I am trying to add conditional format to a column of cells with the
condition refering to a date in the corresponding cell in the previous
column. Is there a way of copying this conditional formatting to other cells
in the column and the formula progressing as it would if the cell had just a
formula in it (i.e. +A1+B1 becomes =A2+B2 in the next row when copied down).
Whenever and however I copy the cell the conditional formatting always refers
to the original cell not the next one down.

 

[Gloria Thrurk]

Check your conditional formatting formula to see if there are dollar signs
"$" preceding the cell references.
The $ makes the cell reference "absolute".
The absence of the $ makes the cell reference "relative".

it's explained here
tls.calpoly.edu/guides/excel/excel_2.doc
Here's the relevant snip

Absolute references If you don't want Excel to adjust references when you
copy a formula to a different cell, use an absolute reference. For example,
if your formula multiplies cell A5 with cell C1 (=A5*C1) and you copy the
formula to another cell, Excel will adjust both references. You can create
an absolute reference to cell C1 by placing a dollar sign ($) before the
parts of the reference that do not change. To create an absolute reference
to cell C1, for example, add dollar signs to the formula as follows:
=A5*$C$1

Switching between relative and absolute references If you created a
formula and want to change relative references to absolute (and vice versa),
select the cell that contains the formula. In the formula bar, select the
reference you want to change and then press F4. Each time you press F4,
Excel toggles through the combinations: absolute column and absolute row
(for example, $C$1); relative column and absolute row (C$1); absolute column
and relative row ($C1); and relative column and relative row (C1). For
example, if you select the address $A$1 in a formula and press F4, the
reference becomes A$1. Press F4 again and the reference becomes $A1, and so
on.


HTH
Gloria

 

[Pete_UK]

What formula have you put in for your conditional formatting? As long
as you do not use absolute references, then the conditions should apply
to other cells if you use the Format Painter.

Pete

 

[Guest]

It may be easier if I give you an example.

In A1:A15 there are dates and in B1:B15 I want to give conditoional
formatting to ensure the date in B1:B15 is between 1 and 28 days after the
corresponding date in A1:A15. In B1 I have used conditional formatting as
follows;

In the first drop down menu choose Formula Is then use the formula
=OR(B1<A1+1,B1>A1+28). If the date in B! is not between 1 and 28 days after
the date in A1 then it will be formatted.

I then want this same condition to apply in B2 but referring to cells B2 and
A2.

I have also used Cell Value Is between .... way of formatting the cell and
the same problem occurs.

When the formatting is either dragged down, format painted or filled down
the conditonal formatting always remains as referring to B1 and A1. I need
the condition to apply to a large number of cells (more like B1:B700) so
don't really want to have to input the conditional formatting for each cell
seperately.

 

[Pete_UK]

Steve,

Highlight all the cells you want the conditional format to apply to
(i.e. B1:B700) then do conditional formatting and set up your formula -
it will automatically adjust to suit different rows.

Hope this helps.

Pete