Formatting a Concatenated Value
Posted by Zeke Crowley on July 24, 2001 1:48 PM
Is there a way to format font in a Concantenated value? Ex: cell A1 contains the project name, and B1 contains the % complete. The concatenated formula is: =concatenate("Project: ",A1,", Progress: ",B1). I would like the values for A1 and B1 to be bold in the resulting concatenated value.
Posted by Nestor on July 26, 2001 3:25 AM
Instead of using a formula to concatenate columns A and B, here's a macro that concatenates them and makes the required characters bold.
Select one or more cells(as required) in column C, and then run the macro.
Sub Concat_Format()
Dim cell As Range
Dim a$, b$, J%, K%
For Each cell In Selection
With cell
a = .Offset(0, -2).Value
b = .Offset(0, -1).Value
J = Len(a)
K = Len(b)
.Value = "Process: " & a & ", Progress: " & b
.Characters(Start:=10, Length:=J).Font.FontStyle = "Bold"
.Characters(Start:=J + 22, Length:=K).Font.FontStyle = "Bold"
End With
Next
End Sub
Alternatively, if you want the value in column C to be updated automatically as and when a new value is input into a cell in column B :-
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Dim a$, b$, J%, K%
a = Target.Offset(0, -1).Value
b = Target.Value
J = Len(a)
K = Len(b)
With Target.Offset(0, 1)
.Value = "Process: " & a & ", Progress: " & b
.Characters(Start:=10, Length:=J).Font.FontStyle = "Bold"
.Characters(Start:=J + 22, Length:=K).Font.FontStyle = "Bold"
End With
End If
End Sub
