0 votes
in VBA by Expert (916 points)
Hello everyone

I have a text like that "Hello-World-Thanks" ..

Using split I can extract the first word Split(strName,"-")(0)

How can I color the second half of the extracted text?

So as in the example the extracted text is "Hello" >> 5 letters >> so 5 / 2 = 2.5 (rounded up to 3) so >> the letters "llo" should be colored

and at the same time keep all the text in the string

Simply the final result will be the same text "Hello-World-Thanks" with "llo" colored

1 Answer

+1 vote
by Expert (801 points)
selected by
Best answer

Assuming you want to display the text in an Excel cell like A1, try this:

strName = "Hello-World-Thanks"
With ActiveSheet.Range("A1")
    .Font.ColorIndex = 1 ' black
    .Value = Split(strName, "-")(0)
    n = Len(.Value)
    k = n / 2
    .Characters((k + 1), (n - k + 1)).Font.ColorIndex = 3 ' red
End With


by Expert (916 points)

Thanks a lot. You put the first word only and I need to put the whole text "Hello-World-Thanks" in A1 for example and color the letters "llo" only

I could fix it by editing the two lines

    .Value = strName
    n = Len(Split(strName, "-")(0))

Thank you very much for great help.

Best Regards

Welcome to wellsr Q&A

Getting Started
VBA Cheat Sheets (On Sale Now)

