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
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.