Fonts and Cells

[Scott]

I have three columns. A1 contains person's first name. B1 contains person's
last name. C1 is a formula that combines A1 and B1, so therefore it would be
Last Name, First Name.

Now my question is, when combining the first two cells, is there a way to
have Last Name a different font than First Name? And a different size?

Thanks.

 

[Gary''s Student]

Copy C1 and paste/special/value back onto C1.

Then you can format parts of the resultant string.

 

[Mike H]

Scott,

No that isn't possible with concatenated text but you could do this. assume
you have concatenated with this

=B1&" "&A1

I.e you have a space between the names. Right click your sheet tab, view
code and paste the code below in. Select Column C and run the code and it
converts the formula to a text string and highlights the last name

Sub HighLight()
For Each c In Selection
c.Value = c.Value
Strt = InStr(c, " ") + 1
With c.Characters(Start:=Strt, Length:=Len(c)).Font
.Size = 12
.ColorIndex = 3
End With
Next
End Sub

Mike

 

[Scott]

Actually, Gary, that does work for me, except that when I make updates to
Columns A1 or B1 the changes don't update in C1.

I was unable to get the coding to register, however.

 

[Mike H]

Scott,

The simple answer is you can't do it unless you change the formula to a
value and as soon as you do that the formula is gone so changes in Column A
or B won't be reflected in Column C.

If you have a lot of these then by far the most effecient way is the code I
posted which if implemented as I described works perfectly bu you will still
have converted formula to values and there's no way around that.

Another approach would be to do the concatenation into column C using code
then if your make changes you would simply run the code again to update
column C. if you want to try this use this code.

Once again, right click your sheet tab, view code and paste the code in and
run it

Sub HighLight()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
c.Offset(, 2).Value = c.Value & " " & c.Offset(, 1).Value
Strt = Len(c) + 1
With c.Offset(, 2).Characters(Start:=Strt, Length:=Len(c.Offset(, 2))).Font
.Size = 12
.ColorIndex = 3
End With
Next
End Sub


Mike