-1

I need to be able to insert a colored symbol while entering text in a cell. I can do it in Word with a macro but in Excel I can only replace the entire cell.

Alternatively is there a way to apply a font style to selected characters (not the whole cell) using vba?

bigbucky
  • 407
  • 6
  • 20
  • Sorry I didn’t make it clear in the question. I want to format the text as I’m writing it like you can do by selecting the characters and using the Font tab. I have to change a character to a symbol font and change the color. It would be confusing to leave the unformatted character and needing to go back and reformat. – bigbucky Jun 15 '18 at 21:14

1 Answers1

0

From a simple use of the macro recorder, you should be able to use .characters().font to add font/color/etc. to a portion of a string:

ActiveCell.FormulaR1C1 = "Text here"
Range("A1").Select
With ActiveCell.Characters(Start:=9, Length:=1).Font
    .Name = "Verdana"
    .FontStyle = "Regular"
    .Size = 9
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
Cyril
  • 6,448
  • 1
  • 18
  • 31
  • Thanks but that replaces the whole cell. In your example I would want to select the "er" in A1 and then run a macro to turn it red. – bigbucky Jun 15 '18 at 18:26
  • @bigbucky i just trimmed the code from the macro recorder to be more direct. In the example code, i changed the first e in "here" to be red. – Cyril Jun 15 '18 at 18:33