How to set different font and color for a text in Word document from Excel macro

I have a macro that can change a text in a Word doc by following code:

   Dim wrdApp As New Word.Application
    Dim wrdDoc As Word.Document
     
    Application.DisplayAlerts = True
    Application.EnableEvents = False
    Application.ScreenUpdating = True
 
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open(template_doc)
    Dim wrdRange As Word.Range

release_version = Range("E4").Value
Result = wrdDoc.Content.Find.Execute(FindText:="{release_version}", ReplaceWith:=release_version, Replace:=Word.WdReplace.wdReplaceAll, .......  more setting ?)

Noted: {release_version} is the key word in Word doc
            :release_version is the variable in macro
                 
Now I want to set different color and fon size under condition of:

if release_version > 10 then
  set text of release_version to Red and font size of 12
else
  set text of release_version to Green and font size of 10
end if

Result = wrdDoc.Content.Find.Execute(FindText:="{release_version}", ReplaceWith:=release_version, Replace:=Word.WdReplace.wdReplaceAll)


tindavidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
I would do it like this:
Sub ReleaseVer()
    Dim wrdApp As New Word.Application
    Dim wrdDoc As Word.Document
    Dim wrdColour As Word.WdColor
    Dim Result As Boolean
    Dim wrdSize As Single
    
    Application.DisplayAlerts = True
    Application.EnableEvents = False
    Application.ScreenUpdating = True
 
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open(template_doc)
    Dim wrdRange As Word.Range

    release_version = Range("E4").Value
    
    Select Case release_version
        Case Is > 10
            wrdSize = 12
            wrdColour = wdColorRed
        Case Else
            wrdSize = 12
            wrdColour = wdColorGreen
    End Select
    
    With wrdDoc.Content.Find
        .Text = "{release_version}"
        .Replacement.Text = release_version
        .Replacement.Font.Color = wrdColour
        .Replacement.Font.Size = wrdSize
        Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
    End With

End Sub

Open in new window

tindavidAuthor Commented:
Dear Graharm,

Thank you for your reply,  the code of changing Font is working but not with the color

It run thru all codes but did not change the color,  why?

Many thanks,

Following are my code: (last 2 comment outed lines are my original code)

    If this_redo_size > base_redo_size Then
       With wrdDoc.Content.Find
          .Text = "{base_redo_size}"
          .Replacement.Text = base_redo_size
          .Replacement.Font.Color = wrdColourSeaGreen
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
       With wrdDoc.Content.Find
          .Text = "{this_redo_size}"
          .Replacement.Text = this_redo_size
          .Replacement.Font.Color = wrdColourRed
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
    Else
       With wrdDoc.Content.Find
          .Text = "{base_redo_size}"
          .Replacement.Text = base_redo_size
          .Replacement.Font.Color = wrdColourRed
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
       With wrdDoc.Content.Find
          .Text = "{this_redo_size}"
          .Replacement.Text = this_redo_size
          .Replacement.Font.Color = wdColorSeaGreen
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
    End If
    'Result = wrdDoc.Content.Find.Execute(FindText:="{base_redo_size}", ReplaceWith:=base_redo_size, Replace:=Word.WdReplace.wdReplaceAll)
    'Result = wrdDoc.Content.Find.Execute(FindText:="{this_redo_size}", ReplaceWith:=this_redo_size, Replace:=Word.WdReplace.wdReplaceAll)
GrahamSkanRetiredCommented:
You seem to have misspelt some of the Word colour constants. Unless you have 'Option Explicit' in the declaration section of the module, an undefined variable return a default value. For a numerical variable the default is 0, which turns out to be black when used as a colour.

This modified code is tested in a Word VBA environment
Option Explicit

Sub recolour()

Dim wrdDoc As Word.Document
Dim Result As Boolean

Set wrdDoc = ActiveDocument
'If this_redo_size > base_redo_size Then
If True Then
       With wrdDoc.Content.Find
          .Text = "{base_redo_size}"
          .Replacement.Text = "base_redo_size"
          .Replacement.Font.Color = wdColorSeaGreen
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
       With wrdDoc.Content.Find
          .Text = "{this_redo_size}"
          .Replacement.Text = "this_redo_size"
          .Replacement.Font.Color = wdColorRed
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
    Else
       With wrdDoc.Content.Find
          .Text = "{base_redo_size}"
          .Replacement.Text = "base_redo_size"
          .Replacement.Font.Color = wdColorRed
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
       With wrdDoc.Content.Find
          .Text = "{this_redo_size}"
          .Replacement.Text = "this_redo_size"
          .Replacement.Font.Color = wdColorSeaGreen
          .Replacement.Font.Size = 12
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
    End If
    'Result = wrdDoc.Content.Find.Execute(FindText:="{base_redo_size}", ReplaceWith:=base_redo_size, Replace:=Word.WdReplace.wdReplaceAll)
    'Result = wrdDoc.Content.Find.Execute(FindText:="{this_redo_size}", ReplaceWith:=this_redo_size, Replace:=Word.WdReplace.wdReplaceAll)
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

tindavidAuthor Commented:
Dear Graham,

Yes, I am able to color it. Just one small  question.

Since I have more than 20 different set of values to be compared and need to set different color. therefore I was trying to put above code into a function, however, every time when the code up to [Marked ABORT below] will abort.  Do you think that I cannot make such call ?  Here is my function call and main body

Function set_color(base_text As String, base_value As Long, this_text As String, this_value As Long)
    If this_value > base_value Then
       With wrdDoc.Content.Find         ' ALWAYS abort after this line of code - ABORT HERE
          .Text = base_text          
          .Replacement.Text = base_value
          .Replacement.Font.Color = wdColorSeaGreen
          .Replacement.Font.Size = 10
          Resul = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
       With wrdDoc.Content.Find
          .Text = this_text
          .Replacement.Text = this_value
          .Replacement.Font.Color = wdColorRed
          .Replacement.Font.Size = 10
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
    Else
       With wrdDoc.Content.Find
          .Text = base_text
          .Replacement.Text = base_value
          .Replacement.Font.Color = wdColorRed
          .Replacement.Font.Size = 10
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
       With wrdDoc.Content.Find
          .Text = this_text
          .Replacement.Text = this_value
          .Replacement.Font.Color = wdColorSeaGreen
          .Replacement.Font.Size = 10
          Result = .Execute(Replace:=Word.WdReplace.wdReplaceAll)
       End With
    End If
End Function

sub main()
.......
........
    base_text = "base_redo_size}"
    base_value = base_redo_size
    this_text = "this_redo_size}"
    this_value = this_redo_size
   
    Result = set_color(base_text, base_value, this_text, this_value)
GrahamSkanRetiredCommented:
Is there an error message? If so what is it?
tindavidAuthor Commented:
Hi Graham,

With wrdDoc.Content.Find         <-- error code (err.number) of 424 with no description
   
GrahamSkanRetiredCommented:
That is an 'Object required' error.

Is the wrdDoc object variable definition within the scope of that code, and is it still instantiated?

Also do you have Option Explicit in the declarations section to catch undefined variables at compile time?

tindavidAuthor Commented:
Excellent solution, thank you Graham.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.