font color of partial cell contents

[Gary Keramidas]

let's say i have a variable called fname (first name)

i want to write that value to a cell and make the font color red. but, there
could already be an fname in the cell, or there could be one added after this
one.

how would i code it to make that particular value appear in red?

 

[Jim Cone]

Hi Gary,
Take a look at the Characters property for the Range object
and also the Characters object.
If you know the length of the text then it's no problem.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Gary Keramidas" <GKeramidasATmsn.com>
wrote in message
let's say i have a variable called fname (first name)
i want to write that value to a cell and make the font color red. but, there
could already be an fname in the cell, or there could be one added after this
one.
how would i code it to make that particular value appear in red?

 

[Gary Keramidas]

i was playing with that before i posted, but couldn't figure out how to only
apply it to the value i am adding to the cell. i may be able to test for upper
case characters and do that, but i was trying to apply it as soon as i place the
value in the cell.

would this work?

i will know the length of the what i am adding. so maybe using the right
function? not sure how to proceed.

 

[Jim Cone]

Adds "Gary" to the end of the existing text in cell B5
and colors it Red. The existing text color is not affected...
'--
Sub JustATest()
Dim strCell As String
Dim strName As String
Dim lngCellLength As Long

strCell = Range("B5").Text
lngCellLength = Len(strCell)

strName = "Gary"
strName = strCell & strName

Range("B5").Value = strName
Range("B5").Characters(lngCellLength + 1, 255).Font.ColorIndex = 3
End Sub
----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Gary Keramidas"
<GKeramidasATmsn.com>
wrote in message
i was playing with that before i posted, but couldn't figure out how to only
apply it to the value i am adding to the cell. i may be able to test for upper
case characters and do that, but i was trying to apply it as soon as i place the
value in the cell.
would this work?

i will know the length of the what i am adding. so maybe using the right
function? not sure how to proceed.

 

[Gary Keramidas]

thanks jim. i'll see if i can work with that.

 

[Gary Keramidas]

jim:

do you know why when i add the line feed it resets the colors?

.Value = .Value & Chr(10) & strName

i put 2 names on a line
as soon as this line executes, the orginal colors change of the 2 names change
to the color of the first listed name.

 

[Guest]

It isn't the line feed per se but the resetting of the value. It just does
that. That's why Jim set the color after setting the value:

< Range("B5").Value = strName
< Range("B5").Characters(lngCellLength + 1, 255).Font.ColorIndex = 3

Instead of changing the cell's Value property (which will wipe out
character-specific formatting), the Characters method allows you to change a
portion of the text formating while preserving the rest. See also the Insert
and Delete methods. Example:

Range("B5").Characters(12, 4).Font.Color = vbRed
Range("B5").Characters(25, 4).Insert ("Gary") 'Supplants existing characters
Range("B5").Characters(25, 0).Insert ("Gary") 'Inserts without supplanting

However, inserting/deleting will only work up to a max of 255 characters
(inclusive of inserted chars). I hope someone will prove me wrong on this or
suggest a workaround because I have a need for it.

Greg

 

[Jim Cone]

Greg,

You can replace characters using the Mid "Statement".
However, I am guessing that is not exactly what you are looking for...

Sub MoreTextStuff()
Dim strOld As String
strOld = Range("B5").Text
Mid$(strOld, 300, 5) = "stuff"
Range("B5").Value = strOld
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Greg Wilson" <[email protected]>
wrote in message
It isn't the line feed per se but the resetting of the value. It just does
that. That's why Jim set the color after setting the value:

< Range("B5").Value = strName
< Range("B5").Characters(lngCellLength + 1, 255).Font.ColorIndex = 3

Instead of changing the cell's Value property (which will wipe out
character-specific formatting), the Characters method allows you to change a
portion of the text formating while preserving the rest. See also the Insert
and Delete methods. Example:

Range("B5").Characters(12, 4).Font.Color = vbRed
Range("B5").Characters(25, 4).Insert ("Gary") 'Supplants existing characters
Range("B5").Characters(25, 0).Insert ("Gary") 'Inserts without supplanting

However, inserting/deleting will only work up to a max of 255 characters
(inclusive of inserted chars). I hope someone will prove me wrong on this or
suggest a workaround because I have a need for it.

Greg

 

[Guest]

Thanks Jim for your response. Unfortunately, I need to insert / delete chars
when there are more than 255 chars without loosing existing
character-specific formatting. And I need this to work elegantly. Saving the
individual formats then looping through after each change and reapplying
won't be adequate.

FWIW, I have created my own "edit mode" (subclassing using PeekMessage)
because I can't have the text wrap when it exceeds the visible range. Using
the formula bar or xl's edit mode causes the text to wrap in this situation.
Obviously, when editing (typing in characters, using backspace or the Delete
key etc.) needs to be very fast and smooth. Currently, as soon as I start
editing, character-specific formating is lost. I can reapply after the edit
but that's not what I want.

Greg