Trying to edit colored text using VBA without losing my different colors upon edit

Trying to create a macro which will add additional text to existing text in a cell, at the beginning of the cell and does not remove the different colors found in the original text upon edit. Was trying this command:

ActiveCell = "String" & ActiveCell

But when I use this, all the different color formatting in my cell goes away upon execution of the edit.

Is there a SendKeys approach I can use such as Edit, go to first part of the cell, and SendKey five spaces?

In line with this, I tried this as an attempted work around:

Application.SendKeys ("{F2}")
Application.SendKeys ("^{Home}")
Application.SendKeys ("Inserted Text")
Application.SendKeys ("Enter")

But it does nothing.

thank you

Hello there,

If I understood you correctly you just want a macro that when selected will add a string to the beginning of the cell? If this is correct then I think this will accomplish what you are trying to do.

While holding down the Alt key your keyboard press F8. This will bring up the Macro window. In the macro name space provided, clear the contents and the type AddString. Then select the Create option.

In between the lines of code

Sub AddString

and

End Sub

paste the following

Dim str As String  'declare variables
    str = ActiveCell.Value  'set the variable str equal to the active cell's value
    ActiveCell.Value = "YourTextHere" & "    " & str  'active cell's value is now equal to the text YourTextHere and what was orginally in the activecell

So the entire code should look like this

Sub AddString()
    
Dim str As String
    str = ActiveCell.Value
    ActiveCell.Value = "YourTextHere" & "    " & str
    
End Sub

Close the VBA Window and to run it select the cell you wish to alter press Alt+F8 again then select the AddString Macro and select run.

Let me know if this works!

Thanks!

Hi, thanks for trying. This is the same as my other solutions . . . it does not work. When the macro executes, I lose all the different colors in my text. My text is multi-colored and I want to keep it multicolored. When I execute your macro, all the color becomes mono-colored. This is why i think a send keys edit solution is the way, because when I manually add text, the colors remain.

Could you post a sample of your workbook? Thanks!

Sure, thx.

Anyone else want to try?

Do the colors of the text stay the same or do they change?

Hey there, alright this seems like a lot so if someone knows another way to do it or if it doesn't work for you let me know. I inserted comments that will help you understand.

Let me know if it works for you.