conditional formatting and insert cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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)
 
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) ..
 
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).
 
Debra Dalgleish said:
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).
Click to expand...

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

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