mystery background color

[Guest]

In some worksheets that I've inherited, I noticed that there seems to
be a way to give sheets a background color other than
Format/Sheet/Background (which uses an image) and
Format/Cells/Patterns.

As is the case of Format/Sheet/Background, this color does not print.

In the workbooks in question, the color does not vary from sheet to
sheet, so it may (or may not) be workbook-wide.

My default window text color in Windows 2000 is white, so that's not
it. In fact, there are two such colored workbooks, and the two colors
are different, although both are variations of gray.

Where might this setting be?

Dan Williams
danwPlanet

 

[dodong]

In some worksheets that I've inherited, I noticed that there seems to
be a way to give sheets a background color other than
Format/Sheet/Background (which uses an image) and
Format/Cells/Patterns.

As is the case of Format/Sheet/Background, this color does not print.

In the workbooks in question, the color does not vary from sheet to
sheet, so it may (or may not) be workbook-wide.

My default window text color in Windows 2000 is white, so that's not
it. In fact, there are two such colored workbooks, and the two colors
are different, although both are variations of gray.

Where might this setting be?

Dan Williams
danwPlanet

 

[dodong]

In some worksheets that I've inherited, I noticed that there seems to
be a way to give sheets a background color other than
Format/Sheet/Background (which uses an image) and
Format/Cells/Patterns.

As is the case of Format/Sheet/Background, this color does not print.

In the workbooks in question, the color does not vary from sheet to
sheet, so it may (or may not) be workbook-wide.

My default window text color in Windows 2000 is white, so that's not
it. In fact, there are two such colored workbooks, and the two colors
are different, although both are variations of gray.

Where might this setting be?

Dan Williams
danwPlanet

 

[dodong]

Maybe the author used a conditional format by using a code. To check
this just right click the sheet then choose view code and you will find
some code in there just like this:

Select Case Target.Value
Case 1
Target.Interior.ColorIndex = 3
Case 2
Target.Interior.ColorIndex = 4
Case Else
Target.Interior.ColorIndex = 5
End Select

 

[Dan Williams]

No, that's the same as Format/Cells/Patterns, except done with VBA.
When I add a color that way and go into Format/Cells/Patterns, the
color is there and I can remove it.

But with these backgrounds, I can't find any way to remove them!
(except to write over them with white or another color).

Dan

 

[Earl Kiosterud]

Dan,

Intriguing. I'm pretty sure you'll have checked this, but in the offending
workbooks, is there a menu item, to wit: Format - Sheet - - Delete
Background? If you set a background image, does it override this ersatz
color? What happens when you remove it?

If you set a cell's background color to "No Color" (Format - Cells -
Patterns), do you get the bogus color, or does it become white?

Are there gridlines? If so, are they really gridlines, or have borders been
set?

Does all this happen no matter how far down or across the worksheet you go?

If you insert rows or columns, do they get the color? How about if you
insert a worksheet?

Your statement about it being or not being workbook-wide wasn't clear to me.
Does this color appear in all the sheets of a given workbook, or just some?

Does setting tab colors have any effect?

Do you instead wish you'd taken up archery?

If you have it in your version of Excel, have you tried "Open and Repair?"
It's in different places. In XL2002, it's in the Open button dropdown in
the File - Open dialog.

I don't know if I'll have any answers, but would like to know these things
I've asked.

 

[Dan Williams]

Thanks, I'll answer in a slightly different order.

Dan,

Intriguing. I'm pretty sure you'll have checked this, but in the offending
workbooks, is there a menu item, to wit: Format - Sheet - - Delete
Background? If you set a background image, does it override this ersatz
color? What happens when you remove it?

Yes, when I tried it, it did not show a Delete Background option, only
the option to add one. When I did add one and delete it, it went back
to it looking gray.

If you set a cell's background color to "No Color" (Format - Cells -
Patterns), do you get the bogus color, or does it become white?

Two cases occur in different sheets. In one, it's already set to No
Color, and selecting it again has no effect. In part of another sheet,
Format - Cells - Patterns thinks it's blue, and when I click OK, it
actually changes it to blue, but when I change it to No Color, it
remains gray.

Does all this happen no matter how far down or across the worksheet you go?
Yes.

If you insert rows or columns, do they get the color? How about if you
insert a worksheet?

Yes. And (I should have thought of this!) yes, a new inserted sheet
has it too!

Your statement about it being or not being workbook-wide wasn't clear to me.
Does this color appear in all the sheets of a given workbook, or just some?

I was just saying that it's in all the sheets, and therefore I was
guessing it's workbook-wide, but that didn't prove it wasn't a
single-sheet setting that happened to be set on all sheets. But
finding the color in a newly-added sheet now proves that it's
definitely workbook-wide.

Does setting tab colors have any effect?

I didn't know what a tab color was, so I looked it up. I'm in Excel
2000, and I gather that it does not have tab colors. But Lotus 1-2-3
had it, which may explain one of the workbooks, which has always been
light gray since the first time I saw it.

I have to confess that the other workbook, which is now sort of
brownish-gray, became that way on a user's copy a few weeks ago while I
was on vacation, and I haven't yet asked her whether she knowingly did
something! I'm guessing that she, in fact, did not -- so I thought I'd
rather research it first, in case she doesn't know how it happened and
she asks me how to fix it. But since there has been no quick answer,
I'll go ahead and ask her.

Are there gridlines? If so, are they really gridlines, or have borders been
set?

There are no set borders in any sheets, but on some sheets I see
gridlines and on others I don't. Perhaps this could be inherited from
Lotus 1-2-3 too, because in copies of the workbook before the change
happened, I see the same gridlines and lack of gridlines even with a
normal white background.

Do you instead wish you'd taken up archery?

Heh heh... No, as you say, troubleshooting is "intriguing".

If you have it in your version of Excel, have you tried "Open and Repair?"
It's in different places. In XL2002, it's in the Open button dropdown in
the File - Open dialog.

In my Excel 2000, I found Detect and Repair, but not Open and Repair.
Detect and Repair seems to want to repair Excel itself rather than the
current document, so I'm afraid to use it. Anyway, the gray is not
Excel-wide.

 

[Earl Kiosterud]

Dan,

In a word, dunno. It sounds as if these were imported from Lotus.
Conversions are sometimes the source of problems. Other than that, I don't
have any suggestions.

I'd ruled out conditional formatting, since you say the mystery color
doesn't print. But on the off-chance that's another problem, have you
looked at that? Format - Conditional Formatting. Also, you might want to
do an Edit - Clear - Formats to some cells, just to see if it buys you
anything. You can always undo it.

Applying a color, including white, causes gridlines to not appear. So
unfortunately, if you do that to get the white cell background back, you'll
lose the gridlines. You can apply light gray borders to get the same
appearance.

My diagnosis: It's messed up.

No charge.

 

[Dan Williams]

Thanks for the effort, anyway.

I can buy the Lotus explanation for the one that was always shaded, but
I dunno about the one that became shaded while I was on vacation.

And it just got even more mysterious. I didn't need to ask the user if
she had done something. I looked at it on her PC, and the backgrounds
are normal there! (As they are on my older archived copies.)

 

[Dan Williams]

AND.................

Today it's back to white again. My archive copies from the previous
period are still gray.