conditional formatting and insert cell

[Guest]

hello everybody

i use excel 2003 and i have this problem :
col A col B
1 2
2 2
3 3
and a conditional formatting like if col B = col A then cell col b is red
in my exemple the cell B2 is red.
if i insert a cell in B2 and i have now this :
col A col B
1 2
2
3 2
3
i have the cell B3 in red and nothing in B2 and in office 2000 the
contionnal formatting are in the new cell and the old cell change is
formatting to A3 and not A2

could you help me?
thanks in advance

 

[Max]

Not really sure but here's one guess ..

Presuming what you're after is
to "preserve" the conditional formatting in col B
to cater for new cell insertions in col B, try this ..

Select col B
(select the entire col)

Click Format > Conditional Formatting

Under Condition 1, make the settings as:
Formula Is: =B1=A1

Click Format button > Font tab > Red/bold? > OK
Click OK at the main dialog

Test it out, you'll find that the Cond Format
remains intact, with the format showing
correctly (i.e. it'll apply only if col B = col A)
even if you were to insert new cells in col B

 

[Debra Dalgleish]

You can use the OFFSET function:

Select column B
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =B1=OFFSET(B1,0,-1)
Click the Format button

 

[Guest]

thanks for your answer but the problem is before insert you have this :
=A2=B2 in the cell : B2 after insert in cell B2 you have =A2=B2 and in B3
you have =A2=B3 and i want =A3=B3 like excel 2000

 

[Debra Dalgleish]

Unfortunately, the behaviour has changed in Excel 2002, and there's no
setting you can change to make it like it was in Excel 2000.

You can use the OFFSET formula, or reformat the column after inserting
cells (click on a cell that has the correct formatting, click the Format
Painter button in the toolbar, click the Column selector button)

 

[Max]

Debra, guess I'm probably missing something here,
but my tests in xl97 does indicate that the CF for col B
was preserved intact (using the CF formula: =B1=A1)
even if cells were to be subsequently inserted in col B
(shift down) ..

 

[Debra Dalgleish]

That's the issue the OP raised.

The conditional formatting worked as you described in Excel 2000 (and
Excel 97), but is different in Excel 2003 (and Excel 2002).

 

[Max]

That's the issue the OP raised.

The conditional formatting worked as you described in Excel 2000 (and
Excel 97), but is different in Excel 2003 (and Excel 2002).

Ah, I see. Thanks .. one of the subtleties of progressive versions

 

[Guest]

thanks

bur just one question Debra : do you know where i can find the behaviour
diference between office 2000 and 2003?

 

[Debra Dalgleish]

There's a list of the advertised version differences here:

http://www.microsoft.com/office/editions/prodinfo/compare.mspx

Subtle differences, such as the one you found, aren't listed.

 

[Guest]

thanks for your help