Databases
StackOverflow2013
Postsdbo
POSubstring colouring from Excel VBA: why do some obvious methods not work?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSubstring colouring from Excel VBA: why do some obvious methods not work?
    text
    copied!<p>I've been building some interesting visualizations that rely on VBA code's ability to set different colours for substrings in Excel. For a cell containing a string the syntax works like this <code>rCell.Characters(start,end).Font.Color=SomeColour</code></p> <p>My application builds the strings and sets the colour values in one step by appending new strings onto the existing values and then setting the colour of the new string. This didn't work. Starting with a complete string and then colouring multiple sub-strings <strong>does</strong> work.</p> <p>Two simple routines illustrate the difference:</p> <pre><code> 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 &amp; "green " rngTestString.Characters(5, 10).Font.Color = RGB(0, 255, 0) rngTestString.Value = rngTestString.Value &amp; "blue" rngTestString.Characters(11, 14).Font.Color = RGB(0, 0, 255) End Sub </code></pre> <p>The two routines result in the two different results shown below: <img src="https://i.stack.imgur.com/vihFN.jpg" alt="the image in two cells from excel"></p> <p>For longer strings with more subsegments it is even worse. I'm using Excel 2010.</p> <p>So is this my fault or is it a bug? Is there a better way to create and colour strings from VBA?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload