Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting - for Expert and Gurus
Hy,
If you have any idea please answer, and don't worry if you could help me on few items. Here there is a series of issue where I get some problems I have a worksheet named "temp.xls" Using the conditional formatting to compare worksheets with this kind of formula in "sheet1": =A1<INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),4,TRUE,"S heet2")) when I save as ... the file "temp.xls" - "temp01.xls" and close it. When I reopen it again I get a big big error "Excel sends report error ..." and whatever... And I have to restart Excel, delete all the conditional formatting reopen it and the file work normally. I think, there is a bug in excel with this kind of conditional formatting because I suspect that excel store some where links to the original workbook name (?). You know this beautiful and impossible to find Phantom link. So when I reopen the renamed book excel can't find the old book and I get the Big error alert! The best would be to recreate in the Sub Workbook_Open() the conditional Formatting, but, in doing this I have another problem ... I loose flexibility The conditional formatting accept only formula in local language (on this point, maybe I'm wrong?) so: ROW in Italy is "rif.riga", in France is "ligne", and so on. Where I can find a "international dictionary" of excel formula? And I know only one way to get the regional setting of excel, maybe you know a better one? I write in a cell .formula="=sum(1,2)" and I read it to find Somma -- Italian; Sum -- English, ... Maybe is better to do my own conditional formatting SUB? Do you think there will be a lack of performances? Or you know a fast way to replicate it by code? Resuming: 1) conditional formatting instability Have you ever experienced this? Do you know any work around? 2) The conditional formatting accept only formula in local language if yes, 3) international dictionary and to how to get local formula language? 4) to Sub or not to Sub? ty, Lorenzo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting - for Expert and Gurus
And I have to restart Excel, delete all the conditional
formatting reopen it and the file work normally. If excel is crashing when you open temp01.xls, how are you able to delete all the conditional formatting, report it etc.? Do you have any other code written in worbook_open even procedure or any other event procedures? Sharad "Lorenzo" wrote in message om... Hy, If you have any idea please answer, and don't worry if you could help me on few items. Here there is a series of issue where I get some problems I have a worksheet named "temp.xls" Using the conditional formatting to compare worksheets with this kind of formula in "sheet1": =A1<INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),4,TRUE,"S heet2")) when I save as ... the file "temp.xls" - "temp01.xls" and close it. When I reopen it again I get a big big error "Excel sends report error ..." and whatever... And I have to restart Excel, delete all the conditional formatting reopen it and the file work normally. I think, there is a bug in excel with this kind of conditional formatting because I suspect that excel store some where links to the original workbook name (?). You know this beautiful and impossible to find Phantom link. So when I reopen the renamed book excel can't find the old book and I get the Big error alert! The best would be to recreate in the Sub Workbook_Open() the conditional Formatting, but, in doing this I have another problem ... I loose flexibility The conditional formatting accept only formula in local language (on this point, maybe I'm wrong?) so: ROW in Italy is "rif.riga", in France is "ligne", and so on. Where I can find a "international dictionary" of excel formula? And I know only one way to get the regional setting of excel, maybe you know a better one? I write in a cell .formula="=sum(1,2)" and I read it to find Somma -- Italian; Sum -- English, ... Maybe is better to do my own conditional formatting SUB? Do you think there will be a lack of performances? Or you know a fast way to replicate it by code? Resuming: 1) conditional formatting instability Have you ever experienced this? Do you know any work around? 2) The conditional formatting accept only formula in local language if yes, 3) international dictionary and to how to get local formula language? 4) to Sub or not to Sub? ty, Lorenzo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formatting - for Expert and Gurus
First, I don't really have an answer.
You may want to look at the way Myrna Larson and Bill Manville did their compa http://www.cpearson.com/excel/whatsnew.htm look for compare.xla They reported differences to another worksheet--not via conditional formatting. KeepItCool has a translation addin at: http://members.chello.nl/keepitcool And this is just a gut feeling. I figure the more conditional formatting used (and I bet you're using a lot), the better chance some type of corruption will occur. (But I think the same thing for regular formatting, comments, shapes, etc. I like plain old data!) And if your workbook is getting close (closer???) to being corrupt, you might want to take a look at OpenOffice. It has a better reputation for opening files that excel can't. (http://www.openoffice.org, a 60-65 meg download or a CD) I've never used it and I'm not sure it supports conditional formatting--but if it does, maybe opening in OpenOffice, saving it there would help "cleanse" the problem. And =indirect() is a volatile function. It'll recalculate whenever excel recalcs. And if you use this in your CF: =A1<INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),4,TRUE,"S heet2")) I would think that there would be a significant hit in performance. If I were doing it via an auto_open, I think I'd just use: =a1<sheet2!a1 If rows/columns are inserted/deleted, then this won't work as well as your formula--but maybe it's still ok. Lorenzo wrote: Hy, If you have any idea please answer, and don't worry if you could help me on few items. Here there is a series of issue where I get some problems I have a worksheet named "temp.xls" Using the conditional formatting to compare worksheets with this kind of formula in "sheet1": =A1<INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),4,TRUE,"S heet2")) when I save as ... the file "temp.xls" - "temp01.xls" and close it. When I reopen it again I get a big big error "Excel sends report error ..." and whatever... And I have to restart Excel, delete all the conditional formatting reopen it and the file work normally. I think, there is a bug in excel with this kind of conditional formatting because I suspect that excel store some where links to the original workbook name (?). You know this beautiful and impossible to find Phantom link. So when I reopen the renamed book excel can't find the old book and I get the Big error alert! The best would be to recreate in the Sub Workbook_Open() the conditional Formatting, but, in doing this I have another problem ... I loose flexibility The conditional formatting accept only formula in local language (on this point, maybe I'm wrong?) so: ROW in Italy is "rif.riga", in France is "ligne", and so on. Where I can find a "international dictionary" of excel formula? And I know only one way to get the regional setting of excel, maybe you know a better one? I write in a cell .formula="=sum(1,2)" and I read it to find Somma -- Italian; Sum -- English, ... Maybe is better to do my own conditional formatting SUB? Do you think there will be a lack of performances? Or you know a fast way to replicate it by code? Resuming: 1) conditional formatting instability Have you ever experienced this? Do you know any work around? 2) The conditional formatting accept only formula in local language if yes, 3) international dictionary and to how to get local formula language? 4) to Sub or not to Sub? ty, Lorenzo -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A problem for gurus, I'd say... | Excel Worksheet Functions | |||
Excel Gurus-Please Help! | Excel Discussion (Misc queries) | |||
Chart Gurus--Please Help | Charts and Charting in Excel | |||
Should be an easy one for you Gurus... | New Users to Excel | |||
Gurus: What's wrong with this code? | Excel Programming |