Automation: Adding Text to Cell With Different Colours

[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

 

[Guest]

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

 

[Guest]

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.