Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
Peter
If the place you copy the cells to already has conditional formatting, after pasting it will have two. If you repeat the pasting of those cells you will continue to amass CF rules, the same ones over and over. It hasn't been an issue for me yet, but, I see what they are complaining about. Seems a litte sloppy. Ken On Jun 26, 3:39*pm, "Peter T" <peter_t@discussions wrote: 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.- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
Peter, You're right. Only complaining does not help. I did the following little excercise to make clear what my problem is : In cells E14:I14 I inserted respectively Yes, Yes, Yes, Part, No. In cells E8:I8 I set conditional formats : If E$14 = "Yes" Borders yes, fill color green If E$14 = "Part" Borders yes, fill color purple If E$14 = "No" No border, no fill color In cells E10:E10 I set conditional formats : If E$14 = "Yes" Borders yes, fill color red If E$14 = "Part" Borders yes, fill color orange If E$14 = "No" Borders yes, no fill color blue So far so good. Then I copied range E8:I8 to E10:I10. If I then go to Manage rules in cell E10, I see six format conditions: If E$14 = "Yes" Borders yes, fill color green If E$14 = "Part" Borders yes, fill color purple If E$14 = "No" No border, no fill color If E$14 = "Yes" Borders yes, fill color red If E$14 = "Part" Borders yes, fill color orange If E$14 = "No" Borders yes, no fill color blue And I expect only the first three. Regards, Henk "Peter T" wrote: 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. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
yikes. its a horrible thing :( Until or IF it gets a fix - and i agree that this is wrong, then Peter T's suggestion kind of makes sense if it weren't for the amount of code work that needs revisiting. the only thing that i could think of was to trap a change on the sheet to remove conditional formatting ... a bit of a sledgehammer approach, will slow the running down quite a bit too i suspect unless you use screenupdating=False already "Henk" wrote in message ... Peter, You're right. Only complaining does not help. I did the following little excercise to make clear what my problem is : In cells E14:I14 I inserted respectively Yes, Yes, Yes, Part, No. In cells E8:I8 I set conditional formats : If E$14 = "Yes" Borders yes, fill color green If E$14 = "Part" Borders yes, fill color purple If E$14 = "No" No border, no fill color In cells E10:E10 I set conditional formats : If E$14 = "Yes" Borders yes, fill color red If E$14 = "Part" Borders yes, fill color orange If E$14 = "No" Borders yes, no fill color blue So far so good. Then I copied range E8:I8 to E10:I10. If I then go to Manage rules in cell E10, I see six format conditions: If E$14 = "Yes" Borders yes, fill color green If E$14 = "Part" Borders yes, fill color purple If E$14 = "No" No border, no fill color If E$14 = "Yes" Borders yes, fill color red If E$14 = "Part" Borders yes, fill color orange If E$14 = "No" Borders yes, no fill color blue And I expect only the first three. Regards, Henk "Peter T" wrote: 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. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
Henk, That's not good at all! What's japanning is when CF's are pasted existing conditions are not removed and new rules added to the old ones (it depends on the respective sets of conditions or 'rules'). Whilst in some circumstances this might be regarded as a useful feature, I agree the process is totally counter to the what one would expect from the Copy function. It should be optional. This has been reported to MS before but possibly not with the steps needed to reproduce (I know it's obvious to you guys but actually it isn't unless spelled out). Realistically it seems unlikely anything will be changed in 2007. I think the best workaround is slightly different to what I suggested previously, namely remove CF's in the destination range *before* copying the source, eg Clear All or Clear Formats or Conditional Formats, Clear rules, from selected cells (ie destination) In code it's only one additional instruction on the destination-range before doing source-range copy. Regards, Peter T "Henk" wrote in message ... Peter, You're right. Only complaining does not help. I did the following little excercise to make clear what my problem is : In cells E14:I14 I inserted respectively Yes, Yes, Yes, Part, No. In cells E8:I8 I set conditional formats : If E$14 = "Yes" Borders yes, fill color green If E$14 = "Part" Borders yes, fill color purple If E$14 = "No" No border, no fill color In cells E10:E10 I set conditional formats : If E$14 = "Yes" Borders yes, fill color red If E$14 = "Part" Borders yes, fill color orange If E$14 = "No" Borders yes, no fill color blue So far so good. Then I copied range E8:I8 to E10:I10. If I then go to Manage rules in cell E10, I see six format conditions: If E$14 = "Yes" Borders yes, fill color green If E$14 = "Part" Borders yes, fill color purple If E$14 = "No" No border, no fill color If E$14 = "Yes" Borders yes, fill color red If E$14 = "Part" Borders yes, fill color orange If E$14 = "No" Borders yes, no fill color blue And I expect only the first three. Regards, Henk "Peter T" wrote: 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. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format disaster in 2007
Ken,
Your message dated 26 June has only just appeared on my server today 10 July. Yes I did pick up on the problem, see my post of 29 June in reply to Henk. I also filed a bug report to MS later the same day. Regards, Peter T "Ken" wrote in message ... Peter If the place you copy the cells to already has conditional formatting, after pasting it will have two. If you repeat the pasting of those cells you will continue to amass CF rules, the same ones over and over. It hasn't been an issue for me yet, but, I see what they are complaining about. Seems a litte sloppy. Ken On Jun 26, 3:39 pm, "Peter T" <peter_t@discussions wrote: 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format 2007 - is this easy? | Excel Discussion (Misc queries) | |||
Conditional Format 2007 | Excel Worksheet Functions | |||
How to conditional format in 3 colors in 2007 | Excel Discussion (Misc queries) | |||
Conditional Format Excel 2007 | Excel Programming | |||
XL 2007 Toolbar Disaster | Excel Discussion (Misc queries) |