Changing the color of part of a string

peedys14

New Member
Joined
Jul 31, 2015
Messages
3
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi
Welcome to the board

Try:

Code:
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
 
Upvote 0
Sorry, forgot to initialise the string.
Disregard last post.

Try:

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top