Indenting Text

I want to indent text in a range of cells based on the 
values contained in another range of cells. Con ditional 
formatting does not apply to indenting so how can I do it?
0
10/17/2003 2:24:14 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
342 Views

Similar Articles

[PageSpeed] 24

I apply data|autofilter, show only those cells that match the condition I want. 
then I select that other column (that actually gets indented) and format those.

Format|Cells|alignment tab|Indent as many as I want.

It doesn't adjust automatically (like conditional formatting), but it's pretty
quick if I need to reapply.

Tony Woods wrote:
> 
> I want to indent text in a range of cells based on the
> values contained in another range of cells. Con ditional
> formatting does not apply to indenting so how can I do it?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/17/2003 2:47:29 AM
Thanks Dave,

I do something similar by sorting the data based on the 
value contained in the value range and then indenting the 
text, after which I reverse the sort. But what I was 
really after was a way to do it "on the fly" as it were, 
so that if one of the values changes the indenting 
automatically changes.

Tony Woods

>-----Original Message-----
>I apply data|autofilter, show only those cells that match 
the condition I want. 
>then I select that other column (that actually gets 
indented) and format those.
>
>Format|Cells|alignment tab|Indent as many as I want.
>
>It doesn't adjust automatically (like conditional 
formatting), but it's pretty
>quick if I need to reapply.
>
>Tony Woods wrote:
>> 
>> I want to indent text in a range of cells based on the
>> values contained in another range of cells. Con ditional
>> formatting does not apply to indenting so how can I do 
it?
>
>-- 
>
>Dave Peterson
>ec35720@msn.com
>.
>
0
10/17/2003 3:33:44 AM
You could use an event macro so that it changes right before your eyes.  The one
I chose depends on the user typing in the value--it doesn't if a formula gets
reevaluated.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    
    With Target
        Select Case LCase(.Value)
            Case Is = "abcd": .Offset(0, 1).IndentLevel = 1
            Case Is = "qwer": .Offset(0, 2).IndentLevel = 2
        End Select
    End With
    
End Sub

I indented column B (.offset(0,1)) one level when the value entered in column A
was "abcd".

And if the value were "qwer", then I indented column C (.offset(0,2)) two
levels.

Another alternative would be to wait for all the data entry to be finished, then
come back and indent from there.

The code would look very similar if you looped through the rows.

Option Explicit
Sub testme02()

    Dim myCell As Range
    Dim myRng As Range
    
    With ActiveSheet
        Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
        myRng.Offset(, 1).IndentLevel = 0
        myRng.Offset(, 2).IndentLevel = 0
        For Each myCell In myRng.Cells
            With myCell
                Select Case LCase(.Value)
                    Case Is = "abcd": .Offset(0, 1).IndentLevel = 1
                    Case Is = "qwer": .Offset(0, 2).IndentLevel = 2
                End Select
            End With
        Next myCell
    End With
    
End Sub

Tony Woods wrote:
> 
> Thanks Dave,
> 
> I do something similar by sorting the data based on the
> value contained in the value range and then indenting the
> text, after which I reverse the sort. But what I was
> really after was a way to do it "on the fly" as it were,
> so that if one of the values changes the indenting
> automatically changes.
> 
> Tony Woods
> 
> >-----Original Message-----
> >I apply data|autofilter, show only those cells that match
> the condition I want.
> >then I select that other column (that actually gets
> indented) and format those.
> >
> >Format|Cells|alignment tab|Indent as many as I want.
> >
> >It doesn't adjust automatically (like conditional
> formatting), but it's pretty
> >quick if I need to reapply.
> >
> >Tony Woods wrote:
> >>
> >> I want to indent text in a range of cells based on the
> >> values contained in another range of cells. Con ditional
> >> formatting does not apply to indenting so how can I do
> it?
> >
> >--
> >
> >Dave Peterson
> >ec35720@msn.com
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/17/2003 9:16:04 PM
Reply:

Similar Artilces:

centering text in text box
Help! I'm doing the school calendar on Publisher 2002, and I can't figure out how to center text in text boxes. It's not like Pub. 97, where you had the center text command. I'm really getting frustrated with Pub. 2002, and I've only used it for 3 days. select the text and click the centering button on the format tool bar. what else are you frustrated with? -- David Bartosik - Microsoft MVP Visit www.davidbartosik.com for Publisher and Web Design Tips and How-to's. "Pat" <pamunds@albertlea.k12.mn.us> wrote in message news:141d01c351e4$ea54df90$a...

Hanging Indent/Left indent marker
I have inherited a ppt presentation and am making some updates, but am having trouble with the hanging indent of bulleted text. For some reason, I cannot change the hanging indent. When I move the left indent marker (the little triangle on the bottom) in the ruler area, it has no effect on the text. The text remains indented approximately 3/8". Even if I drag the marker way out 3 or 4 inches, the text indentation doesn't move at all. Moving the first-line indent marker (the inverted triangle on the top) does move the text in as expected. I created a new text box,...

Text in Text Box won't shift right
PPT 2007 student version. I began with a file made with 97, and cleaned out what I didn't need. I created a new slide with a text box, and cut and pasted text into it from the previous page. If I select the text and try to move it to the right or even put bullets for each short line, not much happens. Oddly for the bulleting, the first line is the only one that receives the bullet. Comments? -- Wayne Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39� 15' 7" ...

Converting Numbers to Text properly
We have part numbers some of which are a mixture of text and numbers an some that are numbers only. Excel sees those parts consisting of onl numbers as numbers rather than text. Even if I format the column t text, the numbers simply move to the left hand side but still se themselves as numbers. I know I can precede a numeric value with a apostrophe to make Excel see the cell contents as text however this i time consuming. Once I have formatted the column to text, I can the double click on each cell containing only numbers and it will then se itself as text but again this is time consuming...

Inserting text based on a user response
Hello, I've had a look at User Forms and articles on toggle buttons, but I'm feeling a bit lost. I don't have any experience with Visual Basic at all. Is there a way to insert sentences into a document based on a user's response to a prompt, for example? I'm building a letter template where the text in the letter will change depending on certain circumstances, e.g. whether a contract has already been extended once or this is the first time it's being extended. If this is not the first time, then there's an extra paragraph to be included in the letter...

Vertical text, font corruption #2
Hi there. I'm trying to draw a vertical text. But I see the font is corrupted. I mean it looks ugly. No antialiasing and so. whe I draw the text horizontal it looks just fine. I'm using "lf.lfEscapement = - 90 * 10;" to make a font vertical and "pDC->TextOut(20, 22, sTitle);" for drawing. lf.lfQuality = ANTIALIASED_QUALITY for both vertical and horizontal. Have now idea what it could be... Any ideas? Thanks. ...

Text formating #2
What I want the line to read Current (Non-Guaranteed) interest rate: 5.00% in year one, 3.00% in years thereafter. What the line is currently reading Current (Non-Guaranteed) interest rate: 5% in year one, 3% in years thereafter. Where the two interest rates are concatenated in a string. How can I make sure that I always have two decimal points shown? try ="Current (Non-Guaranteed) interest rate: " & text(0.05,"0.00%") & " in year one, " & text(A2,"0.00%") & " in years thereafter" if the 5 is always constant and the ....

text form field
I am trying to create a template that has areas that can be filled in. Obviously this can be done in word. Does someone have a link to a vid or something that explains how to create these boxes? Also I want the boxes to be evident that they are there... maybe a line or highlight but that indication should disappear once the form is filled in. See http://word.mvps.org/FAQs/Customization/FillinTheBlanks.htm and especially the forms tutorials by Dian Chapman that this article links to. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http:/...

Indent
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel In previous Word I could hit tab key to increase indentation - to go from 1 to a. Now I have to go to toolbox which takes time. Also, I'd like to have new blank document open up at 150% on opening. Advice? Have a look in Tools> AutoCorrect> AutoFormat As You Type. Confirm that you have checks on Automatic Bulleted/Numbered Lists as well as 'Tabs & backspace set left indent'. To set the Zoom for new docs open the Normal.dotm template, set the preferred Zoom, then type a character or ...

Why does Save As delete header text?
When using Word 2007, I notice that when I open up the Save As dialog box my header and footer texts are erased and replaced with a short text that looks like a template header option. When I enter the new filenamr and click OK, then the newly named file indeed has lost my original header and footer text. What is going on here and how can I keep this from happening? Bob If you are the same person who posted a similar question yesterday, try going back to that thread and reading the replies that were posted. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type ...

How do you add text after a formula?
I'm working on creating a report. At the top of each section is a mont that I type in. At the bottom I want a cell to display that month an also add the word "Total". So for example I have May listed in Cell B3 I now want "May Total" to be listed in cell B36. I know if I want it t display just "May" I'd enter =B3 but how do I make it add the Tota part? Thanks in advance for any help -- Wease ----------------------------------------------------------------------- Weasel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2720 Vie...

Indentation problem with signatures or stationary
I want to use this gif as my stationary http://www.jkador.com/images/backletter.gif but when I start an email left margin is all the way over to the left instead of over out of the way of the graphic. How can I make an indentation or margin default for my signature. ...

Excel---Copying text from one cell a a sheet to another cell on a
I know this will sound stupid but, I cannot get TEXT that I type in one cell on sheet 1 to show up in a cell on sheet 15. Aby help?? If the cell on the 1st sheet is A1, in the cell on Sheet15, you put the formula =Sheet1!A1 On Mon, 18 Oct 2004 19:33:01 -0700, "Reeezzzy" <Reeezzzy@discussions.microsoft.com> wrote: >I know this will sound stupid but, I cannot get TEXT that I type in one cell >on sheet 1 to show up in a cell on sheet 15. Aby help?? ...

Parsing text
Hello- This will probably be an easy one.....here are the details, any help would be greatly appreciated: I have a file that has designer fragrances in a column. The fragrances are listed in the format Fragrance name by Manufcturer name (i.e. Eternity by Calvin Klein, Fahrenheit by Christian Dior, etc). I need to extract the designer name from the string. (Ex. "Eternity by Calvin Klein" appears in the cell, I need the words "Calvin Klein"). I used the following formula in an attempt to acheive this: =IF(ISNUMBER(SEARCH(\"BY\",A1)),RIGHT(A1,LEN(A1)-SEARCH(\"...

Text to columns question
I have some data in the following format: Number firstname lastname schoolname The first three fields are one word each. Schoolname can be more than one word. I'm trying to parse the data using the data -> Text to Columns wizard, but I cannot figure out how to tell the wizard that there are only 4 space delimited columns in my data and not 5 for those instances where the schoolname is two words. Any help is appreciated. Thanks. On Jan 22, 9:50 am, MikeB <MPBr...@gmail.com> wrote: > I have some data in the following format: > > Number firstname lastname schoolname >...

autofit: cell height expands with text entered?
For a form: can a user enter mass quantities of text in a cell and have the cell depth expand so it fits? does Merging Cells limit this ability? I made a giant cell to handle the text the user might enter. I can't figure out where to set this... I have copied & pasted formatting from one worksheet to another without luck. Thanks. Sandy Merged cells don't adjust rowheight for wrapped text (like non-merged cells do). Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like: http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05 Sandy wrote:...

Filtering on text field
I am using an on Double Click event to filter based on the current value on that field. It works when I use it on a number field: Me.Filter = "[forMthYr]=" & Me![cbMthYr] Me.FilterOn = True but when I apply the same code (below) to a text field it does not work - I get parameter boxes popping up. Me.Filter = "[ITResource_LName]=" & Me![tbITResource_LName] Me.FilterOn = True Any help is appreciated. Zag -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1 It works on a numeric field because there are no d...

Document.Save with indents
Hello there, is it possible to write an XML document into a file, which has a readable structure by using indents? By doubleclicking the xml file, you see a readable XML structure in IE, but if you open the file in a text editor, you get an unformatted structure of the XML. Furthermore I would like to show a formatted XML text in a text box: txtResponse.Text = xDoc.OuterXml shows an unformatted text. Regards, Norbert Norbert P�rringer wrote: > is it possible to write an XML document into a file, which has a > readable structure by using indents? Sure, use the Save method w...

HTML gets converted to Plain Text
Hi, We have a web application that sends e-mail messages by being allowed to relay through our smtp server. What's happening is, even though the message is formatted as html, once it gets to the other side, it comes in as plain text. I checked the global settings (this is E2k3), and set MIME to html, and that still didn't work. Is there something else I might be missing? Thanks! ...

Indent
You can indent data to the right. Can you indent data downwards? Hi JJ have no idea what you really mean by this, do you mean increasing the size of the row & aligning the text middle or bottom - if so play with the alignment tab under format / cell - if not maybe you could try again to explain what you want. Cheers JulieD "JJ" <f@o> wrote in message news:%23EugmS$mEHA.3196@TK2MSFTNGP10.phx.gbl... > You can indent data to the right. Can you indent data downwards? > > Changing the row hieght would wrok but we would need to align the data say by percentage.E...

text spilling over text and colums
My text is spilling over the colums and the text. When I change the view from 70% to 75% the text will not show in the row colums, they go out into the white area. If you get this today please feel free to respond. thanks in advanced. Have you tried resizing the column? or perhaps a word wrap? To resize a column at the top by the letter just click the line in between and your mouse pointer should change and allow you drag the column to size. (See pictures 1 and 2) To use the word wrap feature Right-click on the field and select format cells, then click on the alignment tab and you sho...

XmlTextReader problem with &amp; in element text
I have an XSD defined as the following: <?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified"> <xs:element name="Application"> <xs:complexType> <xs:sequence> <xs:element name="ReceivedDate" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> </xs:schema> I have the following XML I am validating against the XSD: <Appl...

How to display a text file dynamically?
I need to show a large text file ( <10M) to users in a dialog. Meanwhile, the file is being appened with more data by a seperate process. I have little experience with MFC. I guess I should use RichEditCtrl for this case. But I'm not sure if I should use SetDlgItemText(..) to send file content to the control. This is Question 1. Secondly, I'd like send only the newly appended part of the file to the control for every refresh, instead of the whole file everytime. Are there any existing functions/mechanisms in MFC for this purpose? By the way, I have to use VC++ for the project (no...

Indenting Bullets #2
Hi, I would like to indent the actual bullets in a Publisher text box, but I can't figure out how. It only allows me to indent the text that follows the bullet, but the bullet stays put. Thank you. Select the text box, select your bulleted list, on the ruler, slide the guides to where you want the indents. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "The Word Source" <The Word Source@discussions.microsoft.com> wrote in message news:C1556385-91...

Setting Text in a ComboBox
Hi, Does anybody know why I cannot set the text in the edit control of a CBS_DROPDOWN style CombBox control using SetWindowText()? I was able to change the text in the edit control part of the ComboBox manually. Thanks, Vincent. I don't know why can't you? Post some code. You should be able to m_MyCombo.SetWindowText("The Text"); Ali R. "Vincent Yu" <anonymous@discussions.microsoft.com> wrote in message news:5e2b01c3ad48$5309d630$a601280a@phx.gbl... > Hi, Does anybody know why I cannot set the text in the > edit control of a CBS_DROPDOWN style ...