Question Locked

Excel '03 - different font colors in one cell leaves gaps between text

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a spreadsheet with a row of headings. In each cell I may have two to five different colors of text, with a comma, or slash between each different colored block of text. The separation between each block is much wider than it is when everything is a single color.

As a way to portray this here, I'm going to use underscores ( _ ) to indicate spacing. A string all in a single color may look like:

IE_6,_7,_8

but if I put the '6', '7', and '8' each in a different color, I get

IE___6,_______7,_____8

The distance between color blocks appears to be random. If I change everything back to a single color, the spacing returns to normal. The text is rotated 90 degrees vertically with the first character at the bottom of the cell. Word wrap is turned on; This problem existed before I installed SP 3.

9 replies

by dawgit

Can you see how the the actual Macro looks when the color is specified? And more precisely how it's different from the default color. You might be able to tweek that with a XML script. I might be off here, but that's where I'd start looking. I hope it helps a bit. -d

by thumbsup2

... I just tried to duplicate what you're seeing and I can not. I typed 6, 7, 8 into a cell and duplicated it across and down, then changed one cell to red/green/blue, each number being different. The spacing did not change. However, Excel's smart formatting wanted to reformat each of the cells to a date. Check to see that yours has not applied some sort of formatting, maybe even that the cell that is all spaced out is not set to "justify" or "distributed". I can force the cell to space things out with the horizontal and/or vertical alignment set to the "distributed" setting.

by charliespencer

The spacing doesn't change unless you have the text rotated to 90 degrees vertical orientation.

I can replicate the problem on a new blank sheet. My Horizontal alignment is set to "General", Vertical is set to "Bottom". I've selected adjoining cells (one all black, one multiple colors) and changed the settings for both at the same time. Any changes produce the expected results except when I rotate the text +90 degrees. Then the spacing goes to pot.

by thumbsup2

The "distributed" formatting applied to Horizontal when text was left to right, but the Vertical applied when text was rotated 90 degrees. After rotating, alignment H "general" and V "bottom" both left the text unspaced.

Nothing I tried would screw up the spacing after rotating, until I appled "distributed". And, that depended on which way the text was aligned.

Do you, perhaps, have some conditional formatting going on in your default template?

How are you applying the colors? Select specific characters within the formula bar then apply color to it? Or, do you have a macro created to do it for you?

Afterthought: Give me exact steps and I'll see if I can duplicate the results. Do you type then tab? Type then press enter? Do you color first, then rotate? Do you rotate, then color? Give me step by step.

by charliespencer

New Blank Workbook
Font - Arial 10 Normal
Click in cell A1
Type "1234 5678 910" and press the Enter key.
Click on cell lower right corner handle, drag while holding CTRL key to copy to adjoining cell B1.
Click on cell B1, click formula bar and highlight "1234"
Click Format, Cell. Because you selected only a portion of the text in the formula bar, the Font tab is the only option available under Format, Cell. Select a color other than 'Automatic', click OK.

At this point you must click outside the cell before rotating, since the Alignment tab is not available. Notice the text moves up from the bottom of the cell slightly when you click outside the cell.

Click B1 again, Format, Cell, Alignment, Orientation, click the black diamond at the top of the orientation dial, click OK. The text should now be improperly spaced.

EDITED - It doesn't make any difference if I rotate before entering the text, if I enter in the formula bar and change colors as I enter each character, if I change fonts.

I did find one setting that affects this. If I change View, Zoom to less than 80%, the text appears properly spaced even multiple colors in a vertical orientation. As soon as I set the Zoom to 80% or higher, the spacing misbehaves. It is not a gradually increasing separation as the Zoom increases. At 79% the spacing is the same for single-color and multi-color text. At 80% the spacing gets screwed up. As the Zoom increases, the improper spacing between the cells remains the same relative to the increase in apparent font size; the spacing does not increase again as the Zoom increases (at least not up to 400%).

If you're really interested I can send you an .XLS file. You'll have to peer mail me first so I can reply with the attachment; we can't append attachments to an original peer mail.

by thumbsup2

I followed your steps exactly, and mine formats just fine.

The key may be in the next to the last paragraph where you said "Notice the text moves up from the bottom of the cell slightly when you click outside the cell." Mine does not do this. I can't duplicate your problem.

I have to get ready for work, so I'll play with it some more tonight.

by thumbsup2

No matter what I do to try and duplicate your problem, I can't. I've enlarged the cell so there's plenty of room to change the spacing if it wants to. I've tried messing with my options. I've tried messing with the zoom between 50% and 100%. I've tried aligning to context, right to left, left to right. I've tried changing the angle of the dangle. I've tried changing colors of individual numbers or even making 8 910 all one color (including the space between), but different than the other numbers. I've tried absolutely everything I can think of and still do not see what you're seeing happen. :(

The only thing I can think of is that you have a default template of some sort stored in your user profile which is causing the problem.

I'll PM you so you can send me a file to look at.

This discussion is closed to new replies.