Coloring substitution from Excel VBA: why some obvious methods don't work?

Question

I created some interesting visualizations that rely on the ability of VBA code to set different color substrings in Excel. For a cell containing a row, the syntax works as follows: rCell.Characters(start,end).Font.Color=SomeColour

My application builds lines and sets color values ​​in one step, adding new lines to existing values ​​and then setting the color of a new line. This did not work. Starting with a full line and then coloring several substrings works .

Two simple procedures illustrate the difference:

  Sub TestColourString1() 'designed to show that substring colour can be done to preexisting string Dim rngTestString As Range Set rngTestString = Range("colour_string") rngTestString.Value = "red green blue" rngTestString.Characters(1, 4).Font.Color = RGB(255, 0, 0) rngTestString.Characters(5, 10).Font.Color = RGB(0, 255, 0) rngTestString.Characters(11, 14).Font.Color = RGB(0, 0, 255) End Sub Sub TestColourString2() 'designed to show that setting colour while building string doesn't work Dim rngTestString As Range Set rngTestString = Range("colour_string") rngTestString.Value = "red " rngTestString.Characters(1, 4).Font.Color = RGB(255, 0, 0) rngTestString.Value = rngTestString.Value & "green " rngTestString.Characters(5, 10).Font.Color = RGB(0, 255, 0) rngTestString.Value = rngTestString.Value & "blue" rngTestString.Characters(11, 14).Font.Color = RGB(0, 0, 255) End Sub 

Two subprograms provide two different results, shown below:

For longer lines with more sub-segments, this is even worse. I am using Excel 2010.

So is it my fault or is it a mistake? Is there a better way to create and color strings from VBA?

Answer 1

The .Value assignment does not magically show how to add to existing data. It deletes old data and places new data.

If the characters were colored, the color of the first character is used to color a new line.

If you want the actual add to be the same as if you manually used the formula bar in Excel, add using .Characters :

 Dim rngTestString As Range Set rngTestString = Range("colour_string") Range("colour_string").Characters(Len(Range("colour_string").Value) + 1).Text = "red " rngTestString.Characters(1, 4).Font.Color = RGB(255, 0, 0) Range("colour_string").Characters(Len(Range("colour_string").Value) + 1).Text = "green " rngTestString.Characters(5, 10).Font.Color = RGB(0, 255, 0) Range("colour_string").Characters(Len(Range("colour_string").Value) + 1).Text = "blue" rngTestString.Characters(11, 14).Font.Color = RGB(0, 0, 255) 

Answer 2

Formatting (including coloring) substrings in a number of excel cells using a macro, see video:

http://youtu.be/O0h6T5Z7HwY