Solved

Modify the spacing within cells in Excel

Posted on 2011-09-27
4
777 Views
Last Modified: 2012-05-12
Hello,

Is there any way to modify the spacing within cells in Excel (2010)?  I have never found any settings to affect this within the normal Excel controls.  However, I am wondering if it can be done through some kind of programming such as VBA.

Specifically, I am referring to the spacing settings which are managed by the Paragraph box in MS Word.  
 aThe settings there are grouped into three sections:

• General alignment -- this section is a non-issue because Excel's alignment settings include all the options found in Word and even more

•  Indentation -- although Excel does have indentation settings which affect the left margin of text, I have never found anything to alter the right margin or create first-line indentation or hanging.

• Spacing -- as far as I know, there are no options to change the inter-line settings in Excel.  Changing the font type and/or size affects the inter-line spacing slightly but customizing the spacing is not possible.

I have very little understanding of VBA but does it have the capability to affect the formatting of text within cells in the ways mentioned above?  As stated initially, I am mostly interested in being able to make changes in the third category:  inter-line spacing.

Thanks
0
Comment
Question by:Steve_Brady
4 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 300 total points
Comment Utility
Steve,

I have found that Excel is not really designed particularly well to handle text publishing as well as MS Word would.

I have had numerous occasions where a report combines numerical data with text but need it formatted better than available in Excel - bullets, indents etc.

I have got round it in the past by embedding a Word document within an Excel sheet or embedding an Excel document within a Word document; depending on which there is the most of, words or numbers.

Alternatively, you can use a table within Word to get column and row alignment of numbers but there isn't the mathematical functions like there is in Excel. You can create the table of data in Excel and then copy and paste as a table, use Paste as RTF if you want the formatting.

Thanks
Rob H
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 100 total points
Comment Utility
Steve_Brady,
I believe the best way to change the inter-line spacing is to add text to different rows and then adjust the row height.  Sorry, I know that's not the answer your were looking for.

http://office.microsoft.com/en-us/excel-help/change-line-spacing-in-excel-HA001034624.aspx

Kyle
0
 
LVL 4

Assisted Solution

by:pgorod
pgorod earned 100 total points
Comment Utility
As far as the VBA you asked for is concerned, you can handle that sort of thing through Range's Interior object, like this:

Worksheets("Sheet1").Range("A1").Interior.ColorIndex = 3

Full options here: http://msdn.microsoft.com/en-us/library/ff839659.aspx

To  format only some characters within a cell, use the Characters() collection. See an example here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=743

However, you won't be able to do much regarding paragraph formatting. Only Font formatting options are available...
0
 

Author Closing Comment

by:Steve_Brady
Comment Utility
Thanks
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now