Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > String Font Color VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default String Font Color VBA
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default String Font Color VBA
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


wrote in message
ups.com...
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default String Font Color VBA
On May 26, 4:20 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

wrote in message

ups.com...



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- Hide quoted text -

- 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default String Font Color VBA
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

" wrote:

On May 26, 4:20 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

wrote in message

ups.com...



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- Hide quoted text -

- 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default String Font Color VBA
On May 26, 11:46 pm, JLGWhiz
wrote:
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



" wrote:
On May 26, 4:20 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

wrote in message

oups.com...

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- Hide quoted text -

- 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- Hide quoted text -

- 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format for font color using font color Jim Excel Worksheet Functions 2 August 29th 09 11:54 AM
Changing Font color based on font type or size John Excel Discussion (Misc queries) 2 February 7th 08 12:50 AM
Check Font or Font color and take action MSPLearner Excel Programming 3 November 15th 06 11:31 AM
selection.font.color returns wrong color; the first execution AnExpertNovice Excel Programming 14 February 7th 06 12:30 PM
My fill color and font color do not work in Excel Std Edition 2003 chapstick Excel Discussion (Misc queries) 1 September 11th 05 08:48 PM


All times are GMT +1. The time now is 01:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement