Insert text (add to) existing text in a cell

[JamesPTuttle]

Hello

Can someone point me in the right direction on how I would fix this macro ?

As a macro CTRL k

In the current active cell - add " (XYZ) " in bold to the end of what ever is already in the cell. Please bear in mind that some cells have several lines of text - so I will need to make sure that as part of the macro, it goes to the very bottom of the cell text prior to inserting the " (XYZ) '

I attached my starting code below - It seems to work however it aways inserts the original text the macro was recorded with, and replaces what current text is in the active cell


Sub Macro3()
'
' Keyboard Shortcut: Ctrl+k
'
ActiveCell.FormulaR1C1 = "This is a test (XYZ)"
With ActiveCell.Characters(Start:=1, Length:=19).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=20, Length:=5).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("I471").Select
End Sub



THANKS for any assistance

James

 

[Joe4]

Excel is not like Word. I don't think you can bold just part of a cell entry (literally, you cannot have multiple formats of the same type in the same cell).

As far as the Range("I471").Select goes, simply remove this line. In your code, you start in the ActiveCell, and you are not moving anywhere from there, so there is no need to select a range at the end.

 

[erik.van.geit]

You can make bold a part of a celltext, unless it contains a formula.

With ActiveCell.Characters(Start:=LEN(ActiveCell)-2, Length:=3).Font
.FontStyle = "Bold" 
End With

this will make the last 3 caracters bold


regards,
Erik

 

[Joe4]

You can make bold a part of a celltext, unless it contains a formula.

I stand corrected! I have been told many times that you couldn't do it, but it appears that you can. Thanks for setting me straight, Eric!

 

[JamesPTuttle]

Gentlemen,

You guys are a BIG help

THANK YOU

I got it working the way I wanted

Below was the final version


' Keyboard Shortcut: Ctrl+k
'
ActiveCell = ActiveCell & " (XYZ)"

With ActiveCell.Characters(Start:=Len(ActiveCell) - 5, Length:=6).Font
.FontStyle = "Bold"
End With

End Sub

 

[erik.van.geit]

jmiskey,
When you never need it, you can't know.

see this example
it makes a title in different colors

Sub insert_name_calceulator()
Range("B2:H2").Select
Selection.Merge
Selection = "calc-EU-lator 9b BE"
ActiveCell.Characters(Start:=1, Length:=4).Font.ColorIndex = 5
ActiveCell.Characters(Start:=5, Length:=1).Font.ColorIndex = xlAutomatic
ActiveCell.Characters(Start:=6, Length:=2).Font.ColorIndex = 3
ActiveCell.Characters(Start:=8, Length:=1).Font.ColorIndex = xlAutomatic
ActiveCell.Characters(Start:=9, Length:=5).Font.ColorIndex = 5
Selection.Font.Size = 24
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.RowHeight = 36
End Sub

regards,
Erik