combining 3 cells in 1 on different lines

[Jack Sons]

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
Ab89-w-5
16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands

 

[Jack Sons]

Joel and Gord,

Thank you both.

The formula I want to do in VBA, I know how.

But, Gord, as I already new, setting the cell content as you adviced will
result in everything in the cell following the characteristics of the first
part, so the whole content becomes red, bold, size 14 and underlined.
That really is my problem.

Any solution?

Jack.

 

[eliano]

Joel and Gord,

Thank you both.

The formula I want to do in VBA, I know how.

But, Gord, as I already new, setting the cell content as you adviced will
result in everything in the cell following the characteristics of the first
part, so the whole content becomes red, bold, size 14 and underlined.
That really is my problem.

Any solution?

Jack.

"Gord Dibben" <gorddibbATshawDOTca> schreef in bericht









- Mostra testo citato -

Hi Jack.

Gord said:
Here is some example code............play with it and see what you can
come
up with.

You can format values, not formula; so try:

Sub CellFont()
ActiveCell = ActiveCell.Value
With ActiveCell.Characters(Start:=1, Length:=7).Font
..ColorIndex = 3
..Bold = True
..Size = 14
End With
With ActiveCell.Characters(Start:=9, Length:=16).Font
..ColorIndex = 5
End With
With ActiveCell.Characters(Start:=18, Length:=17).Font
..Bold = True
..Size = 18
End With
End Sub

Regards
Eliano

 

[Gord Dibben]

Try this.............

Sub CellFont()
With ActiveSheet.Range("A1")
.Value = Range("A2").Value & " " & Chr(10) & _
Range("B2").Value & " " & Chr(10) & _
Format(Range("K2").Value, "dd mmmm yyyy")
With .Characters(Start:=1, Length:=Len(Range("A2").Value)).Font
.Bold = True
.Size = 10
.Name = "Times New Roman"
End With
With .Characters(Start:=Len(Range("A2").Value) + 2, _
Length:=Len((Range("B2").Value)) + (Range("K2").Value)).Font
'Length:= 100).Font
.Name = "Arial"
.Size = 7
End With
End With
End Sub


Gord

 

[Jack Sons]

Gord and Eliano,

Thank you both, it worked very well.
Learned again something .

Jack.

 

[eliano]

Gord andEliano,

Thank you both, it worked very well.
Learned again something .

Jack.

"Gord Dibben" <gorddibbATshawDOTca> schreef in bericht




- Mostra testo citato -

Hi Jack.
Thanks to Gord; I've only played with his indications.
Regards
Eliano