Magic conditional format 1

[segmentationfault]

I have a spreadsheet with some columns that have a background color, lets say columns one and two.

Columns three and four have no background, and when I put in a number or letter, the background immediately becomes the same as in columns one and two. If I set columns one and two to have no background, columns three and four also have no background.

I have used Edit / Go To / Special / Conditional Foramts and there are no cells found whether the entire sheet is selected or not. I can work around this problem by inserting a conditional format to keep columns three and four without a background, but I would prefer to figure out where the background is coming from and fix the real problem.

Any ideas are appreciated.

 

[Zathras]

Add this line to your macro:

  Application.ExtendList = False

See my post in thread68-430555

This seems to be an Excel 2000 thing. I don't think Excel 97 has this "feature"

 

[segmentationfault]

Yeah, it is Excel 2000.

I am using lots of macros in this thing, however I'm pretty sure I did this without using macros. Do you have any idea what I did?

 

[RobBroekhuis]

You can do the same thing manually by going to Tools-Options, and on the Edit tab uncheck the "extend list formats and formulas" item. It's indeed a "feature".
Rob

 

[segmentationfault]

Alright, I did Tools / Options / Edit and turned off the "Extend list formulas and formats". That solves the problem, but if does that option stick with the file or will other people have the same problem as I did before I disabled this?

And what did I do to cause it, and what can I do to remove this "feature" from the sheet in the first place? I've been adding and deleting columns and cells all afternoon...

 

[Zathras]

You probably did nothing. Billy seems to think that the proper default behaviour is to change things automatically so it comes shipped that way.

Not exactly what I call "user friendly."

 

[segmentationfault]

What a bucket of junk.

I just put a Workbook_Open and Workbook_Close macro that turns this misfeature off if necessary and turns it back on upon closing if necessary.

 

[RobBroekhuis]

If you want it to be turned off for your users (who, by the way, may LOVE this feature...), you could put Zathras' code in the workbook_open event handler for your workbook. The nice thing to do would be to save the old setting, and restore it in the workbook_beforeclose event.
Rob

 

[Zathras]

As Rob suggests, there's no accounting for Users' tastes.

It is indeed a great feature for those who want or need it. My only gripe is that forcing it on the users as the default behavior is a little harsh.

Any behavior that surprises the user is by definition unfriendly. Part of the definition of "user friendly" is that the program must behave in a way that can be predicted by the user. (IMHO of course)

 

[segmentationfault]

Hm, that's exactly what I did. If it is turned on when the workbook is opened, I set Excel2000IsRetarded to true and turn off this ExtendList property. Upon closing, if Excel2000IsRetarded, I simply turn the ExtendList property back on. Simple enough, no?

Thanks for the help. Enjoy the star.