String Font Color VBA

  • Thread starter Thread starter Macropheliac
  • Start date Start date
M

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
 
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
 
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 -
Click to expand...

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
 
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
 
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 -
Click to expand...

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
 
Back
Top