Automation: Adding Text to Cell With Different Colours

  • Thread starter Thread starter Manfred Senn
  • Start date Start date
M

Manfred Senn

I'm using VbScript to automate Excel.
The only way I know to programmatically add text with different colours to a
cell is using the 'Characters' function on an existing text:

oExcel.Cells(1, 1) = "This is a Text"
oExcel.Cells(1, 1).Characters(6, 2).Font.ColorIndex = 5
This colors "is" in blue

What I want is a function or a way that colors directly when putting the
text in the cell:
oExcel.Cells(1, 1) = "This "
... then the function that makes text blue
oExcel.Cells(1, 1) = oExcel.Cells(1, 1) & "is "
.... then the function that makes text black
oExcel.Cells(1, 1) = oExcel.Cells(1, 1) & "a Text"

Does anybody know such a function or way?

Manfred
 
Does this help:

Sub diffClrs()
MyString = "This is a text"
With Worksheets(1).Cells(1, 1)
.Value = MyString
.Characters(1, 5).Font.ColorIndex = 5
.Characters(6, 3).Font.ColorIndex = 3
.Characters(9, 6).Font.ColorIndex = 1
End With
End Sub
 
This will apply the colors aftrer text is typed into the cell, but will not
format the cell so that text is colored as it is typed.

Sub FmtDiffClr()
With Worksheets(1).Cells(1, 1)
.Characters(1, 5).Font.ColorIndex = 5
.Characters(6, 3).Font.ColorIndex = 3
.Characters(9, 6).Font.ColorIndex = 1
End With
End Sub

It will not allow you to format the cell using the Characters.Font property
since Characters applies to a string object.
 
Back
Top