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.

chuff
– chuff

2013-07-06 10:38:26 +00:00
Commented Jul 6, 2013 at 10:38 — chuff
– chuff, Commented Jul 6, 2013 at 10:38

ChrisProsser · Accepted Answer · 2013-07-06 12:24:44Z

1

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.

answered Jul 6, 2013 at 12:24

ChrisProsser

13.2k6 gold badges38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francesco Bonizzi · Accepted Answer · 2013-07-07 10:03:35Z

-1

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

answered Jul 7, 2013 at 10:03

Francesco Bonizzi

5,3646 gold badges57 silver badges91 bronze badges

Collectives™ on Stack Overflow

How to format text in Excel while concatenating

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related