I use Excel 2003.
I have a worksheet in which A1="A", B1="B", C1="C".
B1 is bold. D1 has a formula of =A1&B1&C1 and evaluates as "ABC".
The emboldening of B1 is lost in the calculation of D1.
I can see no way of retaining that emboldening.
I am hoping someone can show me how to retain that formatting or prove
to me that it can't be done. ;)
--
Walter Briscoe

I use Excel 2003.
I have a worksheet in which A1="A", B1="B", C1="C".
B1 is bold. D1 has a formula of =A1&B1&C1 and evaluates as "ABC".
The emboldening of B1 is lost in the calculation of D1.
I can see no way of retaining that emboldening.
I am hoping someone can show me how to retain that formatting or
prove to me that it can't be done. ;)

This will require VBA since formulas only work with values.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

In message of Sun, 5 Jan 2014 12:31:55 in
microsoft.public.excel.worksheet.functions, GS
writes
I use Excel 2003.
I have a worksheet in which A1="A", B1="B", C1="C".
B1 is bold. D1 has a formula of =A1&B1&C1 and evaluates as "ABC".
The emboldening of B1 is lost in the calculation of D1.
I can see no way of retaining that emboldening.
I am hoping someone can show me how to retain that formatting or
prove to me that it can't be done. ;)

This will require VBA since formulas only work with values.


Thanks.
I infer you mean event procedures.
I implemented the example worksheet_change example:

Option Explicit

' This example changes the color of changed cells to blue.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 5
End Sub

I change B1 from "B" to "D".
B1 is recolored blue.
D1 is recalculated as "ADC", but not recolored.
My understanding is plainly wrong - I've never used event procedures. ;(

The change event "Occurs when cells on the worksheet are changed by the
user or by an external link."

It may be worth trying the calculate event.

Private Sub Worksheet_Calculate()
Stop
End Sub
is triggered.
Private Sub Worksheet_Calculate()
Cells(1, 4).Font.ColorIndex = 5
End Sub
also acts when B1 is changed.
No other worksheet event seems relevant.
I shall post now to see what further help I might get. ;)
--
Walter Briscoe

Hi Walter,

Am Sun, 5 Jan 2014 21:20:56 +0000 schrieb Walter Briscoe:

I change B1 from "B" to "D".
B1 is recolored blue.
D1 is recalculated as "ADC", but not recolored.

try:

Sub Color()
Range("D1") = Range("A1") & Range("B1") & Range("C1")
Range("D1").Characters(2, 1).Font.Color = Range("B1").Font.Color
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi again,

Am Mon, 6 Jan 2014 07:34:49 +0100 schrieb Claus Busch:

Sub Color()
Range("D1") = Range("A1") & Range("B1") & Range("C1")
Range("D1").Characters(2, 1).Font.Color = Range("B1").Font.Color
End Sub

if all cells have font colors then:
Sub Color()
Dim i As Integer
Range("D1") = Range("A1") & Range("B1") & Range("C1")
For i = 1 To 3
Range("D1").Characters(i, 1).Font.Color = Cells(1, i).Font.Color
Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Walter,

Am Mon, 6 Jan 2014 07:38:21 +0100 schrieb Claus Busch:

Sub Color()
Dim i As Integer
Range("D1") = Range("A1") & Range("B1") & Range("C1")
For i = 1 To 3
Range("D1").Characters(i, 1).Font.Color = Cells(1, i).Font.Color
Next
End Sub

changing format does not fire any event


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Walter,
You can use the Worksheet_Change event to update the target cell. My
1st inclination is to put color~size~bold for the source cells into an
array, then process them as shown by Claus to apply each source cell's
formatting to each character in the target cell, respectively.

You may even include the length of each source cell so formatting the
target matches correctly when source cells contain more than 1
character. This, of course, will dictate how you construct your loop.
I'd start with working with a counter so the loop acts on each element
of the source array, and the counter increments to match the starting
character in the target cell...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = Columns("D").Column Then Exit Sub

'Required to obviate recursion.
Application.EnableEvents = False

Dim vSrc, n&, lChr&, rngSrc As Range
Const lOffset& = -3: Const lResize& = 3
Set rngSrc = Target.Offset(0, lOffset).Resize(1, lResize)
vSrc = rngSrc: lChr = 1

'Required to format characters
Target.Value = Target.Value

For n = LBound(vSrc, 2) To UBound(vSrc, 2)
With Target.Characters(lChr, Len(vSrc(1, n)))
.Font.Size = rngSrc.Cells(n).Font.Size
.Font.Bold = rngSrc.Cells(n).Font.Bold
.Font.Color = rngSrc.Cells(n).Font.Color
End With
'Increment to next start position
lChr = lChr + Len(vSrc(1, n))
Next 'n
Application.EnableEvents = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

For clarity, to resist my 'force-of-habit' the entire procedure need
not have events disable as Claus's 3rd post holds true...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = Columns("D").Column Then Exit Sub

Dim vSrc, n&, lChr&, rngSrc As Range
Const lOffset& = -3: Const lResize& = 3
Set rngSrc = Target.Offset(0, lOffset).Resize(1, lResize)
vSrc = rngSrc: lChr = 1

Application.EnableEvents = False '//required to obviate recursion
Target.Value = Target.Value '//required to format characters
Application.EnableEvents = True

For n = LBound(vSrc, 2) To UBound(vSrc, 2)
With Target.Characters(lChr, Len(vSrc(1, n)))
.Font.Size = rngSrc.Cells(n).Font.Size
.Font.Bold = rngSrc.Cells(n).Font.Bold
.Font.Color = rngSrc.Cells(n).Font.Color
End With
'Increment to next start position
lChr = lChr + Len(vSrc(1, n))
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

In message of Mon, 6 Jan 2014 13:08:59 in
microsoft.public.excel.worksheet.functions, GS
writes
For clarity, to resist my 'force-of-habit' the entire procedure need
not have events disable as Claus's 3rd post holds true...

[snip]

My thanks to both GS and Claus Busch for their valuable thoughts.
Eventually, I decided to write a VBA procedure to fill a column and
embolden parts of the cells.
At first, I had a loop which did both.
That was unhelpful.
Suppose I concatenate strings "A", "B" and "C" in that order and decide
to embolden "B" with code like
Cells(y, x).Characters(Start:=2, Length:=1).Font.Bold = True.
When "C" is concatenated, it is also emboldened as the appended
characters copy the bold attribute from that "B".

I then found my code slow: about 130 seconds for 1300 rows.
I carefully simplified the code with no noticeable result.
I then googled excel vba optimisation.
The first 2 hits were
<www.cpearson.com/excel/optimize.htm and
<http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
Chuck Pearson's site is a gold mine and the Ozgrid site looks useful.
I tried bracketing code with
Application.Calculation = xlCalculationManual and
Application.Calculation = xlCalculationAutomatic
and with
Application.ScreenUpdating = False and
Application.ScreenUpdating = True
The result is that my code runs in 1 to 2 seconds.

That is fast enough. I can't publish as the data is confidential.
--
Walter Briscoe

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com