Haunted cells change color by themselves

[jimmy.void]

Hi All,

I created an excel document and I used it for years. From time to time
I fill in a new row, only with numbers.
Recently I noticed that some of the cells change color when I
introduce any number in them.

To make it simpler, I reduced my excel document to a single sheet just
a few lines and you can find it here:

http://files-upload.com/files/457447/test.xls

The columns that seem to have a strange condition between themselves
are A, C, E, F, H, J
Cells from F, H, J become yellow only if I previously filled in the
cells from A, C, E.

I would like to see what is the condition that makes them changing
colors. Any ideas would be appreciated.

Regards,
Nicolae

 

[Dave Peterson]

Without downloading your file, you may want to look at Format|Conditional
formatting for any of those possessed cells.

 

[jimmy.void]

Thanks Dave, I already checked that.

 

[Gord Dibben]

I attempted to d/l the file but after waiting the required 30 secs then clicking
on download link I get "page not found" error.

If you can resolve that problem, I'll d/l the file.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Did you already check for some sort event macro?

Did you already check to see if you had
Tools|Options|Edit Tab|Extend data range formats and formulas checked?

You may want to list the things you checked so that you get better answers.

 

[Rick Rothstein \(MVP - VB\)]

That is indeed weird! On my copy of the spreadsheet, I seem to be able to
force the color change by clicking in F5, entering 0 in it, hit enter (to
move down to F6) and enter 0 again. When I do this, F6 picks up the color.
It also seems like repeating the enter 0 sequence continually colors every
cell the 0 is entered in. Do you see this same result when you duplicate the
above sequence? If so, it **looks** like selecting every cell (clicking the
empty square where the row/column headers meet), right-clicking the grid,
selecting Format Cells, selecting No Color on the Patter tab and then
clicking OK seems to straighten the spreadsheet out. After do that, I cannot
seem to make the problem happen again. Being that I have no idea why your
grid is doing what it is doing, I can't even begin to say if re-formatting
all the cells is a permanent fix or not.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I attempted to d/l the file but after waiting the required 30 secs then

clicking
on download link I get "page not found" error.

Try it again... I just downloaded the page fine about 5 minutes ago. And the
problem jimmy.void wrote about is weird. See my post to him for a procedure
that forces the problem to appear (at least it does on my system).

Rick

 

[Gord Dibben]

Still no luck Rick.

Must be I'm not doing something right.

Do I need to do anything other than wait for the timer countdown then click on
"Download Link"?


Gord

 

[Rick Rothstein \(MVP - VB\)]

Do I need to do anything other than wait for the timer countdown then

click on
"Download Link"?

That is all I did... as a matter of fact, I just tried it again and the File
Download dialog box popped up just like it is supposed to do. If it matters
any, I am using Internet Explorer 7 on Windows Vista Ultimate Edition.

Rick

 

[Gord Dibben]

WinXp and IE 6 and no go.

Thanks anyway.

That is all I did... as a matter of fact, I just tried it again and the File
Download dialog box popped up just like it is supposed to do. If it matters
any, I am using Internet Explorer 7 on Windows Vista Ultimate Edition.

Rick

 

[Rick Rothstein \(MVP - VB\)]

See if you can download it from my website...

http://www.rickrothstein.com/temp/test.xls

Rick

 

[Debra Dalgleish]

It's a feature that you can turn off:

Choose Tools>Options, and on the Edit tab, remove the check mark from
'Extend data range formats and formulas'

 

[Rick Rothstein \(MVP - VB\)]

Okay, that seems to work. This is a new "feature" for me... where are the
formats being "extended" from? F5 had no format, yet F6 picks one up... from
where?

Rick

 

[Debra Dalgleish]

There's an explanation here:

XL2000: How Auto Extend List Behavior Works
http://support.microsoft.com/kb/231002

 

[Rick Rothstein \(MVP - VB\)]

Well, there's a feature I'll probably not use.<g>

Okay, let me see if I understand... F5 did not pick up a format because,
what, there were not 5 cells above it so the "feature" is not applied? F6,
on the other hand, has 5 cells above it, so the "feature" then looks for
consistent formatting in those 5 cells which, in this case is 3 out of the 5
cells above it?

Rick

 

[Debra Dalgleish]

Like you, most people use the feature because it's turned on by default,
and they aren't aware that it exists. <g>

And that's the way I interpret the article's description of how the
feature works.