Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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 "Jim Cone" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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 "Jim Cone" wrote in message 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
thanks jim. i'll see if i can work with that.
-- Gary "Jim Cone" wrote in message ... 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 "Jim Cone" wrote in message 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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. -- Gary "Jim Cone" wrote in message ... 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 "Jim Cone" wrote in message 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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 "Gary Keramidas" wrote: 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. -- Gary "Jim Cone" wrote in message ... 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 "Jim Cone" wrote in message 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
font color of partial cell contents
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 "Jim Cone" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Partial editing of cell contents. | Excel Discussion (Misc queries) | |||
Partial editing of cell contents. | Excel Discussion (Misc queries) | |||
Problem with partial-cell font formatting | Excel Discussion (Misc queries) | |||
Sorting on partial Cell contents VBA | Excel Discussion (Misc queries) | |||
Partial font color | Excel Discussion (Misc queries) |