I have a VBA macro (code from the internet) that pulls scientific names from an excel sheet (sheet1, range A) and finds/replaces the s.names in the word document with the terms in the excel sheet. The code is supposed to change the font to bold, italic, color, and font type (Times new roman) and this portion works well. What doesn’t work is changing the scientific names to sentence case, i.e. hyla cinerea or Hyla Cinerea or some other variant should be Hyla cinerea.

I’ve tried testing with other functions (e.g., wdUpperCase and wdLowerCase, https://learn.microsoft.com/en-us/office/vba/api/word.wdcharactercase) which work, but are not the desired output. wdTitleSentence is what I want but when the code is run, it doesn’t throw an error and the other formatting is still applied, but nothing changes with the text case unless it’s in all caps, and then it goes to all lowercase.
I also considered writing a function similar to some of the suggestions here: https://www.vbforums.com/showthread.php?847469-RESOLVED-First-letter-in-capital-letter
but I’m not super savvy with VBA, so not sure if there is another existing function that exists that will accomplish this, or something else needs to occur. I did find Excel VBA Sentence Case Function and Converting to sentence case using VBA as potential help, but not sure how to incorporate this into the existing code. I will mention that the text in the excel has the desired case, so if there’s a way to inherit the case from the excel file (and/or other formatting for that matter), that would be great. TIA for the help and I’m a first time poster, so apologies if this isn’t correctly formatted.

VBA word code that works except for changing the case:

Sub format_scientific_names()
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim myarray As Variant
Dim FD As FileDialog
Dim strSource As String
Dim i As Long, lognum As Long
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.Title = "Select the workbook that contains the terms to be italicized"
.Filters.Clear
.Filters.Add "Excel Workbooks", "*.xlsx"
.AllowMultiSelect = False
If .Show = -1 Then
strSource = .SelectedItems(1)
Else
MsgBox "You did not select the workbook that contains the data"
Exit Sub
End If
End With
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
bstartApp = True
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Open(strSource)
Set xlsheet = xlbook.Worksheets(1)
myarray = xlsheet.Range("A1").CurrentRegion.Value
If bstartApp = True Then
xlapp.Quit
End If
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
For i = LBound(myarray) To UBound(myarray)
    Selection.HomeKey wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
    Do While .Execute(FindText:=myarray(i, 1), Forward:=True, _
    MatchWildcards:=True, Wrap:=wdFindStop, MatchCase:=False) = True
    Set rng = Selection.Range
    Selection.Collapse wdCollapseEnd
    rng.Font.Italic = True
    rng.Font.Bold = True
    rng.Font.Color = RGB(200,187,0)
    rng.Font.Name ="Times New Roman"
    rng.Case=  wdTitleSentence 'wdUpperCase works; this doesn't; something else I could put here?
    Loop
    End With
 Next i
End Sub