can we color a cell content partly?

Hi,
My question may look like a simple one but here it goes:

combining cell contents via formulas such as
C10=A20&" ("&B40&")"
is simple and straight forward enough.

But can I manage to display cell B40 contents using a different color (say
red) then cell A20 content in C10 programmatically?. In target cell C10
where cells A20 and B40 contents are to be merged I need to see cell A20
contents in default color, but cell B40 contents in red.
An example will be much appreciated.
TIA
J_J

"J_J" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> My question may look like a simple one but here it goes:
>
> combining cell contents via formulas such as
> C10=A20&" ("&B40&")"
> is simple and straight forward enough.
>
> But can I manage to display cell B40 contents using a different color (say
> red) then cell A20 content in C10 programmatically?. In target cell C10
> where cells A20 and B40 contents are to be merged I need to see cell A20
> contents in default color, but cell B40 contents in red.
> An example will be much appreciated.
> TIA
> J_J
>

this will change appearance of characters [1..3] and [4..5] in cell.
you just need to take care where starts the characters from each merged
cells

With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = 1
End With
With ActiveCell.Characters(Start:=4, Length:=2).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = 3
End With

Following on from sali's suggestion -

Sub MultiFont()
[a1] = "Normal text"
[b1] = "Red text"

With Range("C1")
..Value = Range("A1") & " (" & Range("B1") & ")"
..Font.ColorIndex = xlAutomatic
..Characters(Len(Range("A1")) + 3, Len(Range("B1"))).Font.ColorIndex = 3
End With

End Sub

This puts only the value in a cell (not formula) and doesn't colour the
brackets, adjust as required. How & when you would implement this would
depend on several other factors.

Regards,
Peter T


"J_J" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> My question may look like a simple one but here it goes:
>
> combining cell contents via formulas such as
> C10=A20&" ("&B40&")"
> is simple and straight forward enough.
>
> But can I manage to display cell B40 contents using a different color (say
> red) then cell A20 content in C10 programmatically?. In target cell C10
> where cells A20 and B40 contents are to be merged I need to see cell A20
> contents in default color, but cell B40 contents in red.
> An example will be much appreciated.
> TIA
> J_J
>
>

Thanks sali and Peter...that was cool.

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Following on from sali's suggestion -
>
> Sub MultiFont()
> [a1] = "Normal text"
> [b1] = "Red text"
>
> With Range("C1")
> .Value = Range("A1") & " (" & Range("B1") & ")"
> .Font.ColorIndex = xlAutomatic
> .Characters(Len(Range("A1")) + 3, Len(Range("B1"))).Font.ColorIndex = 3
> End With
>
> End Sub
>
> This puts only the value in a cell (not formula) and doesn't colour the
> brackets, adjust as required. How & when you would implement this would
> depend on several other factors.
>
> Regards,
> Peter T
>
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> My question may look like a simple one but here it goes:
>>
>> combining cell contents via formulas such as
>> C10=A20&" ("&B40&")"
>> is simple and straight forward enough.
>>
>> But can I manage to display cell B40 contents using a different color
>> (say
>> red) then cell A20 content in C10 programmatically?. In target cell C10
>> where cells A20 and B40 contents are to be merged I need to see cell A20
>> contents in default color, but cell B40 contents in red.
>> An example will be much appreciated.
>> TIA
>> J_J
>>
>>
>
>