conditional formatting - for Expert and Gurus

[Lorenzo]

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,"Sheet2"))

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

 

[Sharad Naik]

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

 

[Dave Peterson]

First, I don't really have an answer.

You may want to look at the way Myrna Larson and Bill Manville did their
compare:

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,"Sheet2"))

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.