When you copy paste a cell with conditional formatting to another cell with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to switch that
off and overwrite the existing format as it used to do (and ought to do, I
think).

Something like Application.OverwriteConditionalFormatting = True would be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

I have a file with extensively programmed forms created in Excel 03. One of
its actions copies two cells with conditional formatting to another location.
This may happen multiple time as the "plan" being configured with the form
is edited and saved, then modified further at a later time.

Our company has seen fit to allow users to roll over to 2007 when it suits
them. (wow). Now when the 07 users try to use my template file, they
experience what they call "blowing up". Starting from about 700kb for the
template file, as they go through iterations of editing and saving their
work, we've seen the file size increase to as much as 17MB. Their pc's come
to a grinding halt and production goes to hell in a handbasket.

There are also issues related to calculation that are slowing things to a
crawl even when the files don't blow up. So now I get to turn off automatic
calculation, and find all the places where calculate need to happen to
produce valid results and apply the calculation to only the ranges that need
to give up their results for the immediate purpose.

Thanks Microsoft. (and kudos to my company for the approach to roll-out)

"Henk" wrote:

When you copy paste a cell with conditional formatting to another cell with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to switch that
off and overwrite the existing format as it used to do (and ought to do, I
think).

Something like Application.OverwriteConditionalFormatting = True would be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

Dear Slarbie,

tHenks for your reply. At this moment we seem to be the only ones in the
world forseeing lots of trouble with this totaly insane way of handling
conditional formatting in 2007.

How can???

Regards and all the best,

Henk


"slarbie" wrote:

I have a file with extensively programmed forms created in Excel 03. One of
its actions copies two cells with conditional formatting to another location.
This may happen multiple time as the "plan" being configured with the form
is edited and saved, then modified further at a later time.

Our company has seen fit to allow users to roll over to 2007 when it suits
them. (wow). Now when the 07 users try to use my template file, they
experience what they call "blowing up". Starting from about 700kb for the
template file, as they go through iterations of editing and saving their
work, we've seen the file size increase to as much as 17MB. Their pc's come
to a grinding halt and production goes to hell in a handbasket.

There are also issues related to calculation that are slowing things to a
crawl even when the files don't blow up. So now I get to turn off automatic
calculation, and find all the places where calculate need to happen to
produce valid results and apply the calculation to only the ranges that need
to give up their results for the immediate purpose.

Thanks Microsoft. (and kudos to my company for the approach to roll-out)

"Henk" wrote:

When you copy paste a cell with conditional formatting to another cell with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to switch that
off and overwrite the existing format as it used to do (and ought to do, I
think).

Something like Application.OverwriteConditionalFormatting = True would be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

Use "Paste special" instead

http://office.microsoft.com/en-us/ex...CH100648341033

If this is a specific operation, you can automate paste special with no
format change:

Sub Pst_Spec_Fmla()
With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial Paste:=xlPasteFormulas
End With

End Sub
--
Steve

"Henk" wrote in message
...
When you copy paste a cell with conditional formatting to another cell
with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to switch
that
off and overwrite the existing format as it used to do (and ought to do, I
think).

Something like Application.OverwriteConditionalFormatting = True would be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using
the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

Thanks for you're reply. I understand what you mean, but lots of times I do
want to copy the formats (with or without the formula's). So, it does not
solve the problem.

In my eyes copy paste should overwrite everything in the cell(s) you're
pasting in. This way of doing it is really completely rediculous. I think
Microsoft should do something about this.

Regards,

Henk


"AltaEgo" wrote:


Use "Paste special" instead

http://office.microsoft.com/en-us/ex...CH100648341033

If this is a specific operation, you can automate paste special with no
format change:

Sub Pst_Spec_Fmla()
With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial Paste:=xlPasteFormulas
End With

End Sub
--
Steve

"Henk" wrote in message
...
When you copy paste a cell with conditional formatting to another cell
with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to switch
that
off and overwrite the existing format as it used to do (and ought to do, I
think).

Something like Application.OverwriteConditionalFormatting = True would be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using
the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

Someone else may be able to offer another alternative but choices seem to be
to use VBA to automate conditional formatting and reapply after paste (macro
recorder and tidy the code) or apply formatting with VBA in place of
conditional formats. For the latter:

http://www.ozgrid.com/forum/showthread.php?t=58320

--
Steve

"Henk" wrote in message
...
Thanks for you're reply. I understand what you mean, but lots of times I
do
want to copy the formats (with or without the formula's). So, it does not
solve the problem.

In my eyes copy paste should overwrite everything in the cell(s) you're
pasting in. This way of doing it is really completely rediculous. I think
Microsoft should do something about this.

Regards,

Henk


"AltaEgo" wrote:


Use "Paste special" instead

http://office.microsoft.com/en-us/ex...CH100648341033

If this is a specific operation, you can automate paste special with no
format change:

Sub Pst_Spec_Fmla()
With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial Paste:=xlPasteFormulas
End With

End Sub
--
Steve

"Henk" wrote in message
...
When you copy paste a cell with conditional formatting to another cell
with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to
switch
that
off and overwrite the existing format as it used to do (and ought to
do, I
think).

Something like Application.OverwriteConditionalFormatting = True would
be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using
the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

Steve,

I understand all that, but I have got tons of lines of code that have been
written in 2003. Used in 2007 they do not the same as in 2003. It means weeks
or months of work to detect the lines to be corrected and to correct them.

Copy paste should overwrite any existing formatting. This is like printing
today's paper on yesterday's paper.

Thanks and best regards,

Henk


"AltaEgo" wrote:

Someone else may be able to offer another alternative but choices seem to be
to use VBA to automate conditional formatting and reapply after paste (macro
recorder and tidy the code) or apply formatting with VBA in place of
conditional formats. For the latter:

http://www.ozgrid.com/forum/showthread.php?t=58320

--
Steve

"Henk" wrote in message
...
Thanks for you're reply. I understand what you mean, but lots of times I
do
want to copy the formats (with or without the formula's). So, it does not
solve the problem.

In my eyes copy paste should overwrite everything in the cell(s) you're
pasting in. This way of doing it is really completely rediculous. I think
Microsoft should do something about this.

Regards,

Henk


"AltaEgo" wrote:


Use "Paste special" instead

http://office.microsoft.com/en-us/ex...CH100648341033

If this is a specific operation, you can automate paste special with no
format change:

Sub Pst_Spec_Fmla()
With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial Paste:=xlPasteFormulas
End With

End Sub
--
Steve

"Henk" wrote in message
...
When you copy paste a cell with conditional formatting to another cell
with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to
switch
that
off and overwrite the existing format as it used to do (and ought to
do, I
think).

Something like Application.OverwriteConditionalFormatting = True would
be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using
the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

Copy means Copy and that's what the Copy function does. There are over
thirty different formats that will get copied over; colour, bold, data
validation, etc, etc, including Format conditions. If you don't want the
formats use PasteSpecial. If you want everything except CF's delete them
after, either manually or with a simple macro (though you'll lose undo)

Selection.FormatConditions.Delete

Regards,
Peter T



"Henk" wrote in message
...
When you copy paste a cell with conditional formatting to another cell
with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to switch
that
off and overwrite the existing format as it used to do (and ought to do, I
think).

Something like Application.OverwriteConditionalFormatting = True would be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using
the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.

I'm having the same issue and can't believe it hasn't been resolved in either
service pack.

I have a macro that inserts column for each subcontractor who is bidding a
certain trade (ie plumbing, paint, drywall, etc). The data is pulled from our
bid list workbook and worked like a charm in XL2003. There was quite a bit
of conditional formatting that would highlight scope gaps and highlight the
best bid green, second best yellow and third best orange. Now, in XL2007
whenever I have 10 or more companies it takes literally 3-4 seconds between
key strokes. On bid day, that response time is totally unacceptable. To
resolve I have to clear all conditional formatting from the sheet which is a
pain b/c I have to manually color cells so I know scope issues I have.
Lately, I've just been going back to using Xl2003.

I'm glad a majority of my large office has not "upgraded" to 2007 since 2003
runs these macros without incident.

Someone at Microsoft has to look into this. If you need some control files
to illustrate the issue please contact me and I'll be happy to send some
files with dummy data to test.

I suspect the Excel team are aware of what most of the speed issues but if
you like you can forward your file, ideally with the minimum to demonstrate
the problem. If there is something specific I'll try and look into whether
or not it has been reported.

My address is lightly disguised in the Reply-to field

Regards,
Peter T

"Michael F" wrote in message
...
I'm having the same issue and can't believe it hasn't been resolved in
either
service pack.

I have a macro that inserts column for each subcontractor who is bidding a
certain trade (ie plumbing, paint, drywall, etc). The data is pulled from
our
bid list workbook and worked like a charm in XL2003. There was quite a
bit
of conditional formatting that would highlight scope gaps and highlight
the
best bid green, second best yellow and third best orange. Now, in XL2007
whenever I have 10 or more companies it takes literally 3-4 seconds
between
key strokes. On bid day, that response time is totally unacceptable. To
resolve I have to clear all conditional formatting from the sheet which is
a
pain b/c I have to manually color cells so I know scope issues I have.
Lately, I've just been going back to using Xl2003.

I'm glad a majority of my large office has not "upgraded" to 2007 since
2003
runs these macros without incident.

Someone at Microsoft has to look into this. If you need some control files
to illustrate the issue please contact me and I'll be happy to send some
files with dummy data to test.

I agree with Michael. Someone at Microsoft has to look intio this.

Have nice weekend. Cheers!

Henk


"Peter T" wrote:

I suspect the Excel team are aware of what most of the speed issues but if
you like you can forward your file, ideally with the minimum to demonstrate
the problem. If there is something specific I'll try and look into whether
or not it has been reported.

My address is lightly disguised in the Reply-to field

Regards,
Peter T

"Michael F" wrote in message
...
I'm having the same issue and can't believe it hasn't been resolved in
either
service pack.

I have a macro that inserts column for each subcontractor who is bidding a
certain trade (ie plumbing, paint, drywall, etc). The data is pulled from
our
bid list workbook and worked like a charm in XL2003. There was quite a
bit
of conditional formatting that would highlight scope gaps and highlight
the
best bid green, second best yellow and third best orange. Now, in XL2007
whenever I have 10 or more companies it takes literally 3-4 seconds
between
key strokes. On bid day, that response time is totally unacceptable. To
resolve I have to clear all conditional formatting from the sheet which is
a
pain b/c I have to manually color cells so I know scope issues I have.
Lately, I've just been going back to using Xl2003.

I'm glad a majority of my large office has not "upgraded" to 2007 since
2003
runs these macros without incident.

Someone at Microsoft has to look into this. If you need some control files
to illustrate the issue please contact me and I'll be happy to send some
files with dummy data to test.

Look into what?

Neither you nor Michael have given any details about what you have other
that pasting a lot of CF's.
I just did a copy/paste of 12,000 cells all with CF's in an eye-blink, in a
system with barely the minimum spec's recommended for 2007.

I don't doubt that you are both having problems but it's no good complaining
without giving details of something that can be reproduced to recreate the
problem.

I did offer to look at Michael's file, then check if any related issue has
been reported and if not report it myself.

Actually re-reading the posts it seems yours and Michael's problems are
different, his is speed related whereas you think there should be an
additional set of options to selectively paste the 30+ formats, such as CFs.
Look at the alternatives I suggested, ie PasteSpecial or delete the CFs
after pasting (manually or programmatically).

Regards,
Peter T

"Henk" wrote in message
...
I agree with Michael. Someone at Microsoft has to look intio this.

Have nice weekend. Cheers!

Henk


"Peter T" wrote:

I suspect the Excel team are aware of what most of the speed issues but
if
you like you can forward your file, ideally with the minimum to
demonstrate
the problem. If there is something specific I'll try and look into
whether
or not it has been reported.

My address is lightly disguised in the Reply-to field

Regards,
Peter T

"Michael F" wrote in message
...
I'm having the same issue and can't believe it hasn't been resolved in
either
service pack.

I have a macro that inserts column for each subcontractor who is
bidding a
certain trade (ie plumbing, paint, drywall, etc). The data is pulled
from
our
bid list workbook and worked like a charm in XL2003. There was quite a
bit
of conditional formatting that would highlight scope gaps and highlight
the
best bid green, second best yellow and third best orange. Now, in
XL2007
whenever I have 10 or more companies it takes literally 3-4 seconds
between
key strokes. On bid day, that response time is totally unacceptable.
To
resolve I have to clear all conditional formatting from the sheet which
is
a
pain b/c I have to manually color cells so I know scope issues I have.
Lately, I've just been going back to using Xl2003.

I'm glad a majority of my large office has not "upgraded" to 2007 since
2003
runs these macros without incident.

Someone at Microsoft has to look into this. If you need some control
files
to illustrate the issue please contact me and I'll be happy to send
some
files with dummy data to test.

Peter,

You're absolutely right. Copy should mean Copy and nothing else. Escpecialy
not ADD. And that is exactly what 2007 does. It adds the copied conditional
formatting to the existing condintional formatting at the place where it is
pasted.

It really is a complete mess. Just try it with some conditions over large
ranges. After a few moments you are completele lost. I have seen even double
and triple conditions in "Manage rules".

This is a major problem to me. And to a whole lot of others, I think. The
only do not know yet.

Regards,

Henk


"Peter T" wrote:

Copy means Copy and that's what the Copy function does. There are over
thirty different formats that will get copied over; colour, bold, data
validation, etc, etc, including Format conditions. If you don't want the
formats use PasteSpecial. If you want everything except CF's delete them
after, either manually or with a simple macro (though you'll lose undo)

Selection.FormatConditions.Delete

Regards,
Peter T



"Henk" wrote in message
...
When you copy paste a cell with conditional formatting to another cell
with
conditional formatting, the conditional formatting is added to the new
location. Who the **** did invented that?? Anyone any idea how to switch
that
off and overwrite the existing format as it used to do (and ought to do, I
think).

Something like Application.OverwriteConditionalFormatting = True would be
nice.

Nobody ran into this problem yet?? I have an enormous lot of code using
the
old fashioned overwrite way. Any thoughts and comments about this?

Thanks.