String Font Color VBA

[Macropheliac]

Hello all!

I am wondering how to specify a font color for a string in VBA. For
example, using the following,

aString="Hello World!"
bString="Adios World"
ActiveCell.Value = aString & " " & bString

how would I ammend to make aString Red and Bstring Blue?

Any help is appreciated.

Mac

 

[Gary Keramidas]

one way:

Sub test()
aString = "Hello World!"
bString = "Adios World"
ActiveCell.Value = aString & " " & bString
With ActiveCell
.Font.ColorIndex = 5
.Characters(1, Len(aString)).Font.ColorIndex = 3
End With
End Sub

 

[Macropheliac]

one way:

Sub test()
aString = "Hello World!"
bString = "Adios World"
ActiveCell.Value = aString & " " & bString
With ActiveCell
.Font.ColorIndex = 5
.Characters(1, Len(aString)).Font.ColorIndex = 3
End With
End Sub

--

Gary











- Show quoted text -

Thanks, Gary!

I did adapt your suggestion. However, I am having difficulty applying
it to my situation. Here is the code (from a userform Label_Click
event) I am using.

Private Sub AcceptStatus()
Dim i As Long

For i = 1 To 3
If Me.Controls("Checkbox" & i).Value = True Then aString =
Me.Controls("Checkbox" & i).Caption
Next i

If iStatus = "Work & Reschedule" Then aString = "Work & Reschedule" &
" (" & TextBox3.Text & " for " & TextBox4.Text & ")"



For i = 4 To 10
If Me.Controls("Checkbox" & i).Value = True Then bString =
Me.Controls("Checkbox" & i).Caption
Next i
If Not TextBox1.Text = "" Then bString = TextBox1.Text


cString = Me.TextBox2.Text


ActiveCell.Value = ""

i = Len(ActiveCell.Value)
ActiveCell.Value = aString
ActiveCell.Characters(i + 1).Font.ColorIndex = 3

If Not bString = "" Then
i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & " ~ "
ActiveCell.Characters(i + 1).Font.ColorIndex = 1

i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & itext
ActiveCell.Characters(i + 1).Font.ColorIndex = 11
End If

If Not cString = "" Then
i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & "~"
ActiveCell.Characters(i + 1).Font.ColorIndex = 1

i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & cString
ActiveCell.Characters(i + 1).Font.ColorIndex = 52
End If
End Sub

The problem is each time ActiveCell.Value=ActiveCell.Value & whatever
is used, all of the text to that point becomes the color of the first
text added. Any suggestions?

Thanks,
Mac

 

[Guest]

See if this is what you are looking for:

Sub clrsplt()
cString = "abcdefg"
i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & "~"
ActiveCell.Characters(i + 1).Font.ColorIndex = 1
ActiveCell.Value = ActiveCell.Value & cString
ActiveCell.Characters(i + 2, Len(cString)).Font.ColorIndex = 52
End Sub

To give it more emphasis:

Sub clrsplt()
cString = "abcdefg"
i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & "~"
ActiveCell.Characters(i + 1).Font.ColorIndex = 1
ActiveCell.Value = ActiveCell.Value & cString
ActiveCell.Characters(i + 2, Len(cString)).Font.ColorIndex = 3
End Sub

 

[Macropheliac]

See if this is what you are looking for:

Sub clrsplt()
cString = "abcdefg"
i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & "~"
ActiveCell.Characters(i + 1).Font.ColorIndex = 1
ActiveCell.Value = ActiveCell.Value & cString
ActiveCell.Characters(i + 2, Len(cString)).Font.ColorIndex = 52
End Sub

To give it more emphasis:

Sub clrsplt()
cString = "abcdefg"
i = Len(ActiveCell.Value)
ActiveCell.Value = ActiveCell.Value & "~"
ActiveCell.Characters(i + 1).Font.ColorIndex = 1
ActiveCell.Value = ActiveCell.Value & cString
ActiveCell.Characters(i + 2, Len(cString)).Font.ColorIndex = 3
End Sub




















- Show quoted text -

Thanks JLGWhiz, but I added the following, using the Instr function.
Seems to work quite nicely.


i = InStr(ActiveCell.Value, astring)
If Not i < 1 Then
With ActiveCell.Characters(i, Len(astring))
.Font.ColorIndex = 3
End With
End If

i = InStr(ActiveCell.Value, bstring)
If Not i < 1 Then
With ActiveCell.Characters(i, Len(bstring))
.Font.ColorIndex = 32
End With
End If

i = InStr(ActiveCell.Value, cstring)
If Not i < 1 Then
With ActiveCell.Characters(i, Len(cstring))
.Font.ColorIndex = 10
End With
End If

Thanks to all who provided help.

Mac