CODEWITHSUNDEEP
excelvbastringformatting
Piecevcake
Piecevcake

Reputation: 192

Add formatted symbol after text, keeping character formats of pre-existing text

I want to insert a red tick (or down arrow) after the existing text in a cell.

How do I insert the characters and retain the pre-existing character formats in the cell? Where I have bold, underlined or coloured only some of the words inside those cells.

The generally suggested code reverts all customised character formatting of the original cell contents to the cell font format.

ActiveCell.FormulaR1C1 = ActiveCell & " P "

Upvotes: 3

Views: 2987

Answers (2)

Piecevcake
Piecevcake

Reputation: 192

My final code to insert a tick after text in a cell, with an unformatted space after it, allowing normal text to be added after it later (incorporating the answer above, corrected, and and excel vba select second to last character in a cell):

Sub I___TickRedAFTERText_KeepsOtherCharFormatting()

ActiveCell.Characters(ActiveCell.Characters.Count + 1, 1).Insert (" P ") 'down arrow is " â "

'format the new character but not the added spaces
     With ActiveCell.Characters(ActiveCell.Characters.Count - 1, 1).Font 'second to last character in cell
        .Name = "Wingdings 2"
        .Bold = True
        .Color = -16776961 'from recorded macro
    End With
End Sub

Upvotes: 1

pgSystemTester
pgSystemTester

Reputation: 9897

EDITED: Using ActiveCell.Value or ActiveCell.Formula will indeed change any SPECIAL formatting you previously had (my initial post overlooked that). However, as your research indirectly displayed, you can change the text by using character.insert, but you need to have the character length of where to insert to avoid changing your custom formatting.

Try this instead of value or formula setting. 

ActiveCell.Characters(ActiveCell.Characters.Count + 1, 1).Insert (" P ")

Then you proceed to update the second to last character using a similar approach (this is essentially editing 1 character before the last character).

With ActiveCell.Characters(ActiveCell.Characters.Count -1, Length:=1).Font
        .Name = "Wingdings 2"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
End With

Upvotes: 1

Related Questions