How to format text in Excel while concatenating

Question

I wanted to form a text in a new cell based on input from other cells and in new cell I wanted to highlight selected text with different color, following is my sample code

Sub Macro1()

   While ActiveCell.Offset(0, -1) <> ""       
     ActiveCell.Offset(0, 0).FormulaR1C1 = _
      "New Text" & ActiveCell.Offset(0, -1) & ":" & ActiveCell.Offset(0, -2)

     ActiveCell.Offset(1, 0).Select
   Loop

End Sub

Here I want to define color to "New Text", I have seen how to change for entire cell, but not for a selected text. how to do this?

See [this SO post](http://stackoverflow.com/questions/11672740/how-to-highlight-selected-text-within-excel). — chuff, Jul 06 '13 at 10:38

score 1 · Accepted Answer · answered Jul 06 '13 at 12:24

Here is an example of changing specific characters within a string to another colour (red in this case):

Sub ChgColour()

    vStart = 12
    vLength = 3
    With ActiveCell.Characters(Start:=vStart, Length:=vLength).Font
       .Color = -16776961
    End With

End Sub

This changes the colour of three characters starting from the 12th.

One tip that I have found very useful for getting VBA syntax when I'm not sure is to record a macro whilst doing the steps manually, you can then see the code that was used. Sometimes it generates a little more code than needed, like setting all of the font properties rather than just the one you want, but it is often easy to work out which part you need and replace the absolute cell / character references with relative ones.

score -1 · Answer 2 · answered Jul 07 '13 at 10:03

Use this:

Do While ActiveCell.Offset(0, -1) <> ""
 ActiveCell.Offset(0, 0).FormulaR1C1 = _
  "New Text " & ActiveCell.Offset(0, -1) & ":" & ActiveCell.Offset(0, -2)

 With ActiveCell.Characters(Start:=0, Length:=8).Font
    .Color = -16776961
 End With

 ActiveCell.Offset(1, 0).Select

Loop

How to format text in Excel while concatenating

2 Answers2