Sign in to follow this  
Followers 0
iahngy

Change color of text while writing to a row in excel

18 posts in this topic

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.

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

1 person likes this

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

In AutoIt:

$oExcel.ActiveCell.Characters(1, 3).Font.ColorIndex = 3
$oExcel.ActiveCell.Characters(9, 4).Font.ColorIndex = 5
$oExcel is the Excel application object.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

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 by iahngy

Share this post


Link to post
Share on other sites

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

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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

ActiveCell.FormulaR1C1 = "ABCDEF****"

Share this post


Link to post
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by iahngy

Share this post


Link to post
Share on other sites

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).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 ) ?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

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

Edited by iahngy

Share this post


Link to post
Share on other sites

Glad to be of service :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0