Excel VBA - add formatted symbol to text in a cell

Question

This continues on from my question at excel vba select second to last character in a cell

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

The code I found to insert it, removes the customised character formatting of the original cell contents (bold underlined red etc).

ActiveCell.FormulaR1C1 = ActiveCell & " P "

I can't work out how to insert the characters and retain the pre-existing character formats in the cell (some text red, other text bold, other text may be underlined.)?

I found a macro which replaces characters retaining the existing formats, and wondering if this might be changed to instead add the tick and spaces at the end of the cell, and format them?

I found another macro which copies the format of each character to a new cell after copying the value, at Merge contents of 2 Excel cells keeping character format intact (using VBA), if it could be tweaked to re-format the characters in the original cell... I am a bit stumped!

Or maybe there is an even simpler way?

Any assistance gratefully appreciated.

=============== (The replace macro (edited verson of Prevent reformatting of characters when changing line in cell:)

    STRINGTODELETE = InStr(1, cell.Value, " ") 'define + find string to delete, ANYWHERE in cell (TODO inputbox)
    STRINGTODELETE_LENGTH = 1  'define length of string to delete (TODO get length from input)

    Do Until STRINGTODELETE = 0 'loop within cell until no more occurences found
        cell.Characters(STRINGTODELETE, STRINGTODELETE_LENGTH).Delete 'remove found occurrence of SPECIFIED STRING
        cell.Characters(STRINGTODELETE, 0).Insert Chr(10) 'insert line break where SPECIFIED STRING used to be

Answer 1

for the colour: (for some reason colorindex sometimes returns wrong colours, so I format the cell and create a macro to obtain the colour I want)

.Color = -16776961

Answer 2

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