Merging cells and formats

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

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. ;)
 
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. ;)
Click to expand...

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
 
In message <[email protected]> of Sun, 5 Jan 2014 12:31:55 in
microsoft.public.excel.worksheet.functions said:
This will require VBA since formulas only work with values.
Click to expand...

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. ;)
 
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.
Click to expand...

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.
 
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
Click to expand...

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.
 
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
Click to expand...

changing format does not fire any event


Regards
Claus B.
 
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
 
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
 
In message <[email protected]> of Mon, 6 Jan 2014 13:08:59 in
microsoft.public.excel.worksheet.functions said:
For clarity, to resist my 'force-of-habit' the entire procedure need
not have events disable as Claus's 3rd post holds true...
Click to expand...

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