Changing the color of part of a string

[peedys14]

I am trying to write code which changes the color of text added to a string through an input box. It works great if I only have to add colored text to the cell once, but when I try and add any more information through the input box it resets the color of all the text. What I want to do is keep the color formatting of the text currently in the cell and only change the color of the new text added by the input box if needed. Thank you for any help. Here is my current code:

Private Sub CommandButton1_Click()
Dim Str As String
Dim Cell As Range
Dim i As Integer
Dim x As Integer

Str = InputBox("Enter value to add to selection", "Enter Value")

Text_Color = MsgBox("Make Text Green?", vbYesNo, "Change Text Color")

For Each Cell In Selection

i = Len(Cell)

If Cell.Value = 0 Then
Cell.Value = Str
Else
Cell.Value = Cell.Value & ", " & Str
End If

x = Len(Cell)

If Text_Color = 6 Then
With Cell.Characters(i + 3, x - 1).Font
.Color = RGB(0, 221, 0)
End With
End If

Next Cell
End Sub

 

[pgc01]

Hi
Welcome to the board

Try:

Private Sub CommandButton1_Click()
Dim Str As String
Dim rCell As Range
Dim i As Integer
Dim Text_Color As Variant

Str = InputBox("Enter value to add to selection", "Enter Value")

Text_Color = MsgBox("Make Text Green?", vbYesNo, "Change Text Color")

For Each rCell In Selection

    i = Len(rCell)
    If i > 0 Then Str = " , " & Str
    
    With rCell.Characters(i + 1)
        .Insert Str
        .Font.Color = IIf(Text_Color = vbYes, RGB(0, 221, 0), vbBlack)
    End With
        
Next rCell
End Sub

 

[pgc01]

Sorry, forgot to initialise the string.
Disregard last post.

Try:

Private Sub CommandButton1_Click()
Dim Str As String, Str1 As String
Dim rCell As Range
Dim i As Integer
Dim Text_Color As Variant

Str = InputBox("Enter value to add to selection", "Enter Value")
Str1 = " , " & Str

Text_Color = MsgBox("Make Text Green?", vbYesNo, "Change Text Color")

For Each rCell In Selection

    i = Len(rCell)
    
    With rCell.Characters(i + 1)
        .Insert IIf(i = 0, Str, Str1)
        .Font.Color = IIf(Text_Color = vbYes, RGB(0, 221, 0), vbBlack)
    End With
        
Next rCell
End Sub

 

[peedys14]

Thanks pgc01. Your code didn't directly work, it was having trouble with your .Insert line, it may just be my version. I took your approach and expanded it out and it works great:

Dim Str As String, Str1 As String
Dim rCell As Range
Dim i As Integer
Dim Text_Color As Variant

Str = InputBox("Enter value to add to selection", "Enter Value")
Str1 = " , " & Str

Text_Color = MsgBox("Make Text Green?", vbYesNo, "Change Text Color")

For Each rCell In Selection

i = Len(rCell)

With rCell.Characters(i + 1)
If i = 0 Then
.Insert (Str)
Else
.Insert (Str1)
End If

If Text_Color = vbYes Then
.Font.Color = RGB(0, 175, 0)
Else
.Font.Color = vbBlack
End If
End With

Next rCell

Thanks again.

 

[pgc01]

I'm glad your problem is solved. Thanks for posting your solution.