I need help bolding and underlining cells that are "Concatenated". For example, cell format for cell K12 is already bolded and underlined. If I concatenate (C1,K12,D2) I get a result that doesn't carry over the format of those individual cells - particularly K12 which I want BOLDED and UNDERLINED. Can anyone please help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
BOLDING and UNDERLINING CONCATENATED CELLS
- Thread starter GNoel
- Start date
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
Hello, Gnoel,
when using formulas you cannot "divide" the format
using some code you can paste the different values and copy their formats
I did only succeed using a (tiny) space between the values
try this
kind regards,
Erik
when using formulas you cannot "divide" the format
using some code you can paste the different values and copy their formats
I did only succeed using a (tiny) space between the values
try this
Code:
Option Explicit
Sub test()
Call concatenate_cells_formats(Range("A1"), Range("C1,K12,D2"))
End Sub
Sub concatenate_cells_formats(cell As Range, source As Range)
'Erik Van Geit
'060424
Dim c As Range
Dim i As Integer
i = 1
With cell
.Value = vbNullString
.ClearFormats
For Each c In source
.Value = .Value & " " & c
Next c
.Value = Trim(.Value)
For Each c In source
With .Characters(Start:=i, Length:=Len(c)).Font
.Name = c.Font.Name
.FontStyle = c.Font.FontStyle
.Size = c.Font.Size
.Strikethrough = c.Font.Strikethrough
.Superscript = c.Font.Superscript
.Subscript = c.Font.Subscript
.OutlineFont = c.Font.OutlineFont
.Shadow = c.Font.Shadow
.Underline = c.Font.Underline
.ColorIndex = c.Font.ColorIndex
End With
.Characters(Start:=i + Len(c), Length:=1).Font.Size = 1
i = i + Len(c) + 1
Next c
End With
End SubErik
Upvote
0
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
Re: Concatenate..
first try out the code
later we can change it to your suits...
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code:
to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)
assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T
best regards,
Erik
the answer was in my reply: you can't
therefore you recieved some code to do this
first try out the code
later we can change it to your suits...
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code:
to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)
assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T
best regards,
Erik
Upvote
0
Underlining concatenated cells
I've tried the macro and the result is not what I expected.
When I have the formula ... =CONCATENATE (B1,C1,D1) ... and run the macro....the result is contents of A1 is replaced by contents of C1.
This is awesome. Outstanding work. Can you help me though with my real problem.
If B1 contains "He "
C1 contains "loves "
D1 contains "her."
If I type in cell E1 the formula =CONCATENATE (B1,C1,D1)....I want the result in cell E1 to appear as ..... He loves her.
But I want loves to be underlined or in bold or both.
Thanks.
I've tried the macro and the result is not what I expected.
When I have the formula ... =CONCATENATE (B1,C1,D1) ... and run the macro....the result is contents of A1 is replaced by contents of C1.
This is awesome. Outstanding work. Can you help me though with my real problem.
If B1 contains "He "
C1 contains "loves "
D1 contains "her."
If I type in cell E1 the formula =CONCATENATE (B1,C1,D1)....I want the result in cell E1 to appear as ..... He loves her.
But I want loves to be underlined or in bold or both.
Thanks.
Upvote
0
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
Hi,
for the third time I will answer the same
you can NOT use a formula and have part of the contents in another format
you can copy different formats in one cell WITHOUT formula
you can edti the code from above to your needs
change this line
Call concatenate_cells_formats(Range("A1"), Range("C1,K12,D2")) Call
regarding your last question the code should be
if you can make this work for you, we can try to automate the process if you want
(example; whenever the contents of a cell in columns B, C or D is changed, column E would be updated)
best regards,
Erik
for the third time I will answer the same
you can NOT use a formula and have part of the contents in another format
you can copy different formats in one cell WITHOUT formula
you can edti the code from above to your needs
change this line
Call concatenate_cells_formats(Range("A1"), Range("C1,K12,D2")) Call
Code:
concatenate_cells_formats(Range("A1"), Range("C1,K12,D2"))
Code:
concatenate_cells_formats(Range("E1"), Range("B1,C1,D1"))if you can make this work for you, we can try to automate the process if you want
(example; whenever the contents of a cell in columns B, C or D is changed, column E would be updated)
best regards,
Erik
Upvote
0
tempaccount
New Member
- Joined
- Aug 19, 2011
- Messages
- 8
bump
I need this macro to work for multiple rows. In your example A1 is the cell where final concatenated object would go. I want this formula to work for all cells in column A or if possible whenever I highlight a cell and click the macro shortcut it will work for that row and output it into that rows A cell.
(eg I click row A and press macro shortcut and the concatenated value with same format is outputted in A3).
I need this macro to work for multiple rows. In your example A1 is the cell where final concatenated object would go. I want this formula to work for all cells in column A or if possible whenever I highlight a cell and click the macro shortcut it will work for that row and output it into that rows A cell.
(eg I click row A and press macro shortcut and the concatenated value with same format is outputted in A3).
Upvote
0
Similar threads
- Solved
- Question
