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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"klyyss" <(E-Mail Removed)> wrote in message
news:54CF6A09-00E4-4A99-9712-(E-Mail Removed)...
> 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

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
On the Patterns tab, choose the Red colour
Click OK, click OK

klyyss wrote:
> 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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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)

klyyss wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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) ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Debra Dalgleish <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
> klyyss wrote:
> > 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
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>

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 wrote:
> 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) ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
> Debra Dalgleish <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>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)
>>
>>klyyss wrote:
>>
>>>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
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish <(E-Mail Removed)> wrote
> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

thanks

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

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

http://www.microsoft.com/office/edit...o/compare.mspx

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

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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html