Merging cells and formats

[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.

 

[GS]

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

 

[Walter Briscoe]

In message <[email protected]> of Sun, 5 Jan 2014 12:31:55 in

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.

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[GS]

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

 

[GS]

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

 

[Walter Briscoe]

In message <[email protected]> of Mon, 6 Jan 2014 13:08:59 in

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.