Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default different colour font in same cel

I have a cell in excel containing a string of text with some characters in
black some in green and some in red. I want to remove all the black text
leaving only the red and green. Can this be done?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,203
Default different colour font in same cel

Not based on color with worksheet functions. It could be done with VBA code
(a macro) but it would be ugly code to write.

I'm guessing there's more than on cell involved? Otherwise it would be just
as easy to just manually edit the darned thing.

"ILMER57" wrote:

I have a cell in excel containing a string of text with some characters in
black some in green and some in red. I want to remove all the black text
leaving only the red and green. Can this be done?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default different colour font in same cel

Many thanks for your answer - I was afraid that there would not be a supplied
function to do this. You are right, I have hundreds of cells like this and
they are altered as the spread sheet develops. I suppose one option could be
to copy the cells into WORD and then use the search and replace options in
that program but that gets messy. Is the macro option out of the question
and if not can the question be pursued here or should it be asked in another
forum?


"JLatham" wrote:

Not based on color with worksheet functions. It could be done with VBA code
(a macro) but it would be ugly code to write.

I'm guessing there's more than on cell involved? Otherwise it would be just
as easy to just manually edit the darned thing.

"ILMER57" wrote:

I have a cell in excel containing a string of text with some characters in
black some in green and some in red. I want to remove all the black text
leaving only the red and green. Can this be done?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,203
Default different colour font in same cel

If you're going to pursue it, I'd pursue it in the Programming forum.
Someone more familiar with coding with colored text in a cell may be able to
come up with something pretty nifty.
It actually would not have been too difficult if you wanted to get rid of
the red and green, but the black was an issue when I tried it: once you
remove a word, then all of the text turns black and you have to "remember"
what was green/red and reset those colors for the text.


"ILMER57" wrote:

Many thanks for your answer - I was afraid that there would not be a supplied
function to do this. You are right, I have hundreds of cells like this and
they are altered as the spread sheet develops. I suppose one option could be
to copy the cells into WORD and then use the search and replace options in
that program but that gets messy. Is the macro option out of the question
and if not can the question be pursued here or should it be asked in another
forum?


"JLatham" wrote:

Not based on color with worksheet functions. It could be done with VBA code
(a macro) but it would be ugly code to write.

I'm guessing there's more than on cell involved? Otherwise it would be just
as easy to just manually edit the darned thing.

"ILMER57" wrote:

I have a cell in excel containing a string of text with some characters in
black some in green and some in red. I want to remove all the black text
leaving only the red and green. Can this be done?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,203
Default different colour font in same cel

I couldn't let it go. I did a little thinking and came up with the following
code that appears to work.

To use the code, first make a copy of your workbook and work with that copy
to test this. Open the copy of the workbook, press [Alt]+[F11] to open the
VB Editor and choose Insert -- Module. Copy the code below and paste it
into the new module. Close the VB Editor. Select all of the cells you want
to process to remove all Black Text and then run the macro. It even reduces
long strings of spaces to a single space, hope you wanted that.

Sub RemoveBlackText()
Const myBlack = xlAutomatic

Dim strText() As String
Dim intColorIndex() As Integer

Dim groupRange As Range
Dim anyCell As Range
Dim TLC As Long ' text loop counter
Dim newText As String
Dim completedFlag As Boolean
Dim cleanUpLoop As Long

Set groupRange = Selection
For Each anyCell In groupRange
Debug.Print anyCell.Address
ReDim strText(1 To 1)
ReDim intColorIndex(1 To 1)
newText = ""
If Not IsEmpty(anyCell) And _
Trim(anyCell) < "" Then
For TLC = 1 To Len(anyCell)
'don't even save the black text!
'but must preserve spaces
If anyCell.Characters(TLC, 1).Font.ColorIndex < myBlack _
Or Mid(anyCell, TLC, 1) = " " Then
strText(UBound(strText)) = Mid(anyCell, TLC, 1)
intColorIndex(UBound(intColorIndex)) = _
anyCell.Characters(TLC, 1).Font.ColorIndex
ReDim Preserve strText(1 To UBound(strText) + 1)
ReDim Preserve intColorIndex(1 To UBound(intColorIndex) + 1)
End If ' anyCell.Text... text
Next ' TLC loop
End If ' end test for empty cells

If UBound(strText) 1 Then
'had some non-black text
ReDim Preserve strText(1 To UBound(strText) - 1)
ReDim Preserve intColorIndex(1 To UBound(intColorIndex) - 1)
'deal with sequences of blanks
completedFlag = False ' kickstart the loop
Do While Not completedFlag
completedFlag = True ' try to end it
For TLC = 2 To UBound(strText)
If strText(TLC) = " " And _
strText(TLC - 1) = " " Then
For cleanUpLoop = TLC To UBound(strText) - 1
strText(cleanUpLoop) = strText(cleanUpLoop + 1)
intColorIndex(cleanUpLoop) = intColorIndex(cleanUpLoop + 1)
strText(cleanUpLoop + 1) = ""
intColorIndex(cleanUpLoop + 1) = xlAutomatic
completedFlag = False
Next ' cleanUpLoop end
End If ' test for " "
Next ' TLC loop
Loop ' completedFlag loop
For TLC = LBound(strText) To UBound(strText)
newText = newText & strText(TLC)
Next
End If
'put newText back into the cell
anyCell = newText
'now set the colors properly
If Len(newText) 0 Then
For TLC = LBound(intColorIndex) To UBound(intColorIndex)
anyCell.Characters(TLC, 1).Font.ColorIndex = intColorIndex(TLC)
Next
End If
Next ' anyCell loop
End Sub



"ILMER57" wrote:

Many thanks for your answer - I was afraid that there would not be a supplied
function to do this. You are right, I have hundreds of cells like this and
they are altered as the spread sheet develops. I suppose one option could be
to copy the cells into WORD and then use the search and replace options in
that program but that gets messy. Is the macro option out of the question
and if not can the question be pursued here or should it be asked in another
forum?


"JLatham" wrote:

Not based on color with worksheet functions. It could be done with VBA code
(a macro) but it would be ugly code to write.

I'm guessing there's more than on cell involved? Otherwise it would be just
as easy to just manually edit the darned thing.

"ILMER57" wrote:

I have a cell in excel containing a string of text with some characters in
black some in green and some in red. I want to remove all the black text
leaving only the red and green. Can this be done?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
textBox font colour the same as cell font colour???????? Sophie Excel Discussion (Misc queries) 4 February 13th 09 11:15 AM
Change font colour [email protected] Excel Discussion (Misc queries) 3 December 23rd 06 08:07 PM
Font colour numbers JC Excel Discussion (Misc queries) 2 October 7th 06 01:14 PM
Font colour phil2006 Excel Discussion (Misc queries) 1 July 31st 06 02:15 PM
can the fill colour of a bar be tied to the data font colour data PaulC Charts and Charting in Excel 0 June 23rd 06 01:21 AM


All times are GMT +1. The time now is 10:27 PM.

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

About Us

"It's about Microsoft Excel"