Justify line text Independently

[Codman]

I have a number of cells, each containing text split over separate lines using Alt-Return. I wish to right justify the top line and centre justify the other but can't seem to get the right control. I don't think this can be done by using Cell custom formatting. not can I see how to do this using VBA. My pitiful approach is below. Can someone point me in the right direction?

Sub Dave()
'
' Dave Macro
'

'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "CH01" & Chr(10) & "PIPE 201" & Chr(10) & "" & Chr(10) & "LUBE" & Chr(10) & "LOW"
    Range("B2").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

 

[strongm]

Unfortunately, whilst you can apply font formatting - bold, italic, etc, to a set of characters (such as a line of text) in a cell, alignment is applied to the whole cell (whether through custom formatting, or through VBA)

One alternative might be to use a text box, which supports what you want to do.

 

[Codman]

Hi Strongm,

Thanks for confirming what I though may be the case. Oh well, back to the drawing board

 

[Andrzejek]

I guess you can do this in VBA:

with fixed width font, like [tt]Courier New[/tt]. Just add appropriate number of Spaces in front of first line of text. My example has 4 Spaces before CH01, but you would have to calculate it on-the-fly

Option Explicit

Sub Dave()

With Range("B2")
    .Value = [blue]Space(4) & [/blue]"CH01" & Chr(10) & "PIPE 201" & Chr(10) & "LUBE" & Chr(10) & "LOW"
    .HorizontalAlignment = xlCenter
End With
    
End Sub

---- Andy

There is a great need for a sarcasm font.