CODEWITHSUNDEEP
excelvbams-wordformattingconcatenation
Smcspeedy
Smcspeedy

Reputation: 1

Format text within a concatenated string

I have a formula that is going to be pulling information from multiple cells on my spreadsheet and concatenating them together to create what will eventually be a "Project Brief" - a document that outlines all of the information from those cells in an easy-to-read word document.

Below is a sample of the VBA code I'm working with. In this code you'll see I concatenate things like "Company: " and then the name of the company pulled from the spreadsheet. How can I make it so when I output this information to a cell, or a word document, just the word "Company: " is bolded? or formatted in any way (size, color, etc.)?

Sub ProjectBrief()

' Pull together information from the sheet to create a project brief.


' ----------- Error Checking ----------- '

On Error Resume Next

' ----------- Information ----------- '

' PROJECT NAME

Dim PROJECT_NAME As String

If Range("B" & ActiveCell.Row) = vbNullString _
    Then PROJECT_NAME = "" _
    Else: PROJECT_NAME = _
"Project Name: " & _
Range("B" & ActiveCell.Row) & _
" " & Chr(10)

' COMPANY

Dim COMPANY As String

If Range("C" & ActiveCell.Row) = vbNullString _
    Then COMPANY = "" _
    Else: COMPANY = _
"Company: " & _
Application.WorksheetFunction.Index(Range("TCompanyName"), _
Application.WorksheetFunction.Match(Range("C" & (ActiveCell.Row)), Range("TCompanyID"), 0), 1) & _
" " & Chr(10)


' ----------- Final Concatenate ----------- '

Dim FINAL As String
FINAL = "Basic Information" & Chr(10) & PROJECT_NAME & COMPANY

' ----------- Create Word Doc ----------- '

Range("F4").Value = FINAL

End Sub

Upvotes: 0

Views: 323

Answers (1)

macropod
macropod

Reputation: 13490

If you add formatting tags such as <b> & </b> before and after the content you want to apply the formatting to, you could run code like the following after writing your strings to the Word document:

With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Forward = True
    .MatchWildcards = True
    .Wrap = wdFindContinue
    .Replacement.Text = "\1"
    .Text = "<u>(*)</u>"
    .Replacement.Font.Underline = True
    .Execute Replace:=wdReplaceAll
    .ClearFormatting
    .Text = "<b>(*)</b>"
    .Replacement.Font.Bold = True
    .Execute Replace:=wdReplaceAll
    .ClearFormatting
    .Text = "<i>(*)</i>"
    .Replacement.Font.Italic = True
    .Execute Replace:=wdReplaceAll
  End With
End With

The above code allows for bold (b), italic (i), and underline (u) formatting individually or in combination. Other tags could be added.

For paragraph-level formatting, though, you really should be applying the appropriate Word Style definitions to the paragraphs concerned. As with the foregoing character attributes, tags for Style definitions could be used for that (e.g. <Style=Heading1> & <Style>) with a corresponding '.Replacement.Style = ' in the code).

Upvotes: 1

Related Questions