Change color of text while writing to a row in excel

[iahngy]

Hi ,

Is it possible to change color of a char among characters while AutoIt is writing to a row in Excel?

for ex, I am writing ABC then I wnt to change to red for the next char W ...so ABC will be black and W will be red...all in 1 row.

[water]

Start the macro recorder in Excel and do what you want to do.

Then post the VBA code here. We will then try to translate it to AutoIt.

[jdelaney]

google: excel vba multicolor text

first result

ActiveCell = "Red and Blue"

ActiveCell.Characters(Start:=1, Length:=3).Font.ColorIndex = 3

ActiveCell.Characters(Start:=9, Length:=4).Font.ColorIndex = 5

[water]

In AutoIt:

$oExcel.ActiveCell.Characters(1, 3).Font.ColorIndex = 3
$oExcel.ActiveCell.Characters(9, 4).Font.ColorIndex = 5

$oExcel is the Excel application object.

[iahngy]

In this macro, at start, i set font to courier new and bold and color as black ..then I typed ABCD ..then change font color to orange for EF then change back to black for the stars

expandcollapse popup

Sub Macro4()
'
' Macro4 Macro
'

'
With Selection.Font
.Name = "Courier New"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
ActiveCell.Select
ActiveCell.FormulaR1C1 = "ABCDEF****"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Courier New"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=5, Length:=2).Font
.Name = "Courier New"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=7, Length:=4).Font
.Name = "Courier New"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="Macro4"
End Sub

Edited February 18, 2013 by iahngy

[water]

I normally pass $row and $col to the function ..i dont use R1C1 ...

So you want to set the active cell by your script?

[iahngy]

Yes...I m trying to write the code base on the macro...hihi...i lost the previous macro which has the code for orange color...

[iahngy]

how do i set value in autoit from this macro ' ....i pass $row, $col to a funct.

ActiveCell.FormulaR1C1 = "ABCDEF****"

[water]

In this macro, at start, i set font to courier new and bold and color as black ..then I typed ABCD ..then change font color to orange for EF then change back to black for the stars

But as you can see from the recorded macro Excel does it the other way round. First the cell is filled with the value and then the characters are formated.

[water]

how do i set value in autoit from this macro ' ....i pass $row, $col to a funct.

ActiveCell.FormulaR1C1 = "ABCDEF****"

Use

$oExcel.Activesheet.Cells($Row, $Col).Value = $Value

[iahngy]

I got it work ...Thank you H20 !!..by this I know how to record macro heheh.

func writex($row,$col, $oExcel)
with $oExcel
.cells($row, $col).font.name = "Courier New"
.cells($row, $col).font.Bold = True
.cells($Row, $col).Value = 'ABCDEF***'
.cells($Row, $col).Characters(1,4).font.colorindex = 32
.cells($Row, $col).Characters(5,6).font.colorindex = 42
.cells($Row, $col).Characters(7,9).font.colorindex = 32


EndWith

EndFunc

Edited February 18, 2013 by iahngy

[water]

Glad your problem could be solved.

Be careful when you take code created by the Excel recorder and translate it to AutoIt. The recorder uses selections (that's what you see on the screen when clicking on a cell). If you do a lot of processing selections slow down your script because every change is displayed on the screen. Use ranges for better performance.

Have a look at the Excel UDF that comes with AutoIt or my rewrite of the Excel UDF (still in an early alpha release).

[iahngy]

use range like R1C1 ? instead of passing $row, $col?

you think that code will be ok for writing to excel for about 200 cells just using ($row, $col ) ?

[iahngy]

Hi Water,

I copied from excel.au3 the range function to set font as below, ..what if i dont know in advance the end of the row for a range because i dont know how long data is from a source..how can i set the font w/o knowing the range? in this ex, i assume rowend is 300 rows..but what if i dont know the row end ...how can i set the whole sheet then?

.activesheet.range($oExcel.cells(1,1), $oExcel.cells(300,10)).font.name = "Courier New"

this labtop wont let met paste in autoit window

[water]

you think that code will be ok for writing to excel for about 200 cells just using ($row, $col ) ?

For 200 cells you shouldn't note any difference.

[water]

Hi Water,

I copied from excel.au3 the range function to set font as below, ..what if i dont know in advance the end of the row for a range because i dont know how long data is from a source..how can i set the font w/o knowing the range? in this ex, i assume rowend is 300 rows..but what if i dont know the row end ...how can i set the whole sheet then?

.activesheet.range($oExcel.cells(1,1), $oExcel.cells(300,10)).font.name = "Courier New"

this labtop wont let met paste in autoit window

If you know the columns you could use something like this:

.activesheet.range("A:G").font.name = "Courier New"

sets the font for columns A to G.

[iahngy]

Thank you H20..This way from A to G is perfect.

Edited February 19, 2013 by iahngy

[water]

Glad to be of service