I have a formula which copies the text in cell A2 into B2. The text is typed
in A2 in two different colours but when it is copied into B2 it shows in one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes the
colour of that character only and not the remaining characters in the cell

copy and paste should give you two colours

"Nipun" wrote:

I have a formula which copies the text in cell A2 into B2. The text is typed
in A2 in two different colours but when it is copied into B2 it shows in one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes the
colour of that character only and not the remaining characters in the cell

As long as it's NOT a formula you can do anything you like. Or, convert the
formula to a value and then do it.
To do with a macro, look in the vba help index for CHARACTER or record doing
it to see what happened.

--
Don Guillett
SalesAid Software

"Nipun" wrote in message
...
I have a formula which copies the text in cell A2 into B2. The text is
typed
in A2 in two different colours but when it is copied into B2 it shows in
one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes
the
colour of that character only and not the remaining characters in the cell

Thanks Don, but i want it as a formula because i may keep changing the value
of the original cell... i didnt understand what you meant by the macro
feature...
thanks
Nipun

"Don Guillett" wrote:


As long as it's NOT a formula you can do anything you like. Or, convert the
formula to a value and then do it.
To do with a macro, look in the vba help index for CHARACTER or record doing
it to see what happened.

--
Don Guillett
SalesAid Software

"Nipun" wrote in message
...
I have a formula which copies the text in cell A2 into B2. The text is
typed
in A2 in two different colours but when it is copied into B2 it shows in
one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes
the
colour of that character only and not the remaining characters in the cell

You can't have different colors in a cell that contains a formula.

--
Don Guillett
SalesAid Software

"Nipun" wrote in message
...
Thanks Don, but i want it as a formula because i may keep changing the
value
of the original cell... i didnt understand what you meant by the macro
feature...
thanks
Nipun

"Don Guillett" wrote:


As long as it's NOT a formula you can do anything you like. Or, convert
the
formula to a value and then do it.
To do with a macro, look in the vba help index for CHARACTER or record
doing
it to see what happened.

--
Don Guillett
SalesAid Software

"Nipun" wrote in message
...
I have a formula which copies the text in cell A2 into B2. The text is
typed
in A2 in two different colours but when it is copied into B2 it shows
in
one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes
the
colour of that character only and not the remaining characters in the
cell

one way:

Since you can't have multiple font colors in a cell containing a
formula, replace the formula with an event macro:

Put this Event macro in your worksheet code module (right-click the
worksheet tab and choose view Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
With Range("A2:B2")
If Not Intersect(Target, .Cells(1)) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
.Cells(2).Value = .Cells(1).Value
Application.EnableEvents = True
On Error GoTo 0
For i = 1 To Len(.Cells(1).Text)
.Cells(2).Characters(i, 1).Font.ColorIndex = _
.Cells(1).Characters(i, 1).Font.ColorIndex
Next i
End If
End With
End Sub


In article ,
Nipun wrote:

I have a formula which copies the text in cell A2 into B2. The text is typed
in A2 in two different colours but when it is copied into B2 it shows in one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes the
colour of that character only and not the remaining characters in the cell

Thank You for replying... I understood your logic... I think it should
work... Im new to macros so please can you help me with executing it in
detail...

"JE McGimpsey" wrote:

one way:

Since you can't have multiple font colors in a cell containing a
formula, replace the formula with an event macro:

Put this Event macro in your worksheet code module (right-click the
worksheet tab and choose view Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
With Range("A2:B2")
If Not Intersect(Target, .Cells(1)) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
.Cells(2).Value = .Cells(1).Value
Application.EnableEvents = True
On Error GoTo 0
For i = 1 To Len(.Cells(1).Text)
.Cells(2).Characters(i, 1).Font.ColorIndex = _
.Cells(1).Characters(i, 1).Font.ColorIndex
Next i
End If
End With
End Sub


In article ,
Nipun wrote:

I have a formula which copies the text in cell A2 into B2. The text is typed
in A2 in two different colours but when it is copied into B2 it shows in one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes the
colour of that character only and not the remaining characters in the cell

this line changes your formula to a value which you said that you did NOT
want. Again, you can NOT do what you want. "People in hell want ice water"

.Cells(2).Value = .Cells(1).Value


--
Don Guillett
SalesAid Software

"Nipun" wrote in message
...
Thank You for replying... I understood your logic... I think it should
work... Im new to macros so please can you help me with executing it in
detail...

"JE McGimpsey" wrote:

one way:

Since you can't have multiple font colors in a cell containing a
formula, replace the formula with an event macro:

Put this Event macro in your worksheet code module (right-click the
worksheet tab and choose view Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
With Range("A2:B2")
If Not Intersect(Target, .Cells(1)) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
.Cells(2).Value = .Cells(1).Value
Application.EnableEvents = True
On Error GoTo 0
For i = 1 To Len(.Cells(1).Text)
.Cells(2).Characters(i, 1).Font.ColorIndex = _
.Cells(1).Characters(i, 1).Font.ColorIndex
Next i
End If
End With
End Sub


In article ,
Nipun wrote:

I have a formula which copies the text in cell A2 into B2. The text is
typed
in A2 in two different colours but when it is copied into B2 it shows
in one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes
the
colour of that character only and not the remaining characters in the
cell

Thanks for your response. I managed to get what i want by using your
suggestion of recording a macro.

Thank you very much for your time and energy.

cheers

Nipun

"Don Guillett" wrote:

this line changes your formula to a value which you said that you did NOT
want. Again, you can NOT do what you want. "People in hell want ice water"

.Cells(2).Value = .Cells(1).Value


--
Don Guillett
SalesAid Software

"Nipun" wrote in message
...
Thank You for replying... I understood your logic... I think it should
work... Im new to macros so please can you help me with executing it in
detail...

"JE McGimpsey" wrote:

one way:

Since you can't have multiple font colors in a cell containing a
formula, replace the formula with an event macro:

Put this Event macro in your worksheet code module (right-click the
worksheet tab and choose view Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
With Range("A2:B2")
If Not Intersect(Target, .Cells(1)) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
.Cells(2).Value = .Cells(1).Value
Application.EnableEvents = True
On Error GoTo 0
For i = 1 To Len(.Cells(1).Text)
.Cells(2).Characters(i, 1).Font.ColorIndex = _
.Cells(1).Characters(i, 1).Font.ColorIndex
Next i
End If
End With
End Sub


In article ,
Nipun wrote:

I have a formula which copies the text in cell A2 into B2. The text is
typed
in A2 in two different colours but when it is copied into B2 it shows
in one
colour. Is it possible for it to show it in the same colours or is it
possible for me to have a function that locates a character and changes
the
colour of that character only and not the remaining characters in the
cell