Excel: Change different of colors text in same cell

[siawpo]

Hi,

I'd like to change different colors for different portion of text in same cell of Excel application.

Neither character length nor cell might not fixed.

Here's the code I've tried to put together but not manage to pull it off.

I'm appreciate it for any suggestion, thank you.

$oExcel = ObjCreate("Excel.Application")
With $oExcel ; open new workbook
    .Visible = True
    .WorkBooks.Add
    .ActiveWorkbook.Sheets(1).Select()
EndWith

  $oExcel.Cells.Font.Color = 0x000000

  $oExcel.ActiveFont.Color = -16776961
   Send ("'I'd like this sentence to be red'")
   Sleep(100)

   Send ("{AltDown}{Enter}{AltUp}")
   Sleep(100)

  $oExcel.ActiveCell.Selection.Font.Color = 0x000000
   Send ("'I like this sentence to be black'")
   Sleep(100)

   Send ("{AltDown}{Enter}{AltUp}")
   Sleep(100)

   $oExcel.ActiveFont.Color = -16776961
   Send ("'I'd like this sentence to be red again'")
   Sleep(100)

   Send ("{AltDown}{Enter}{AltUp}")
   Sleep(100)

   Send("{ENTER}")

 

[Subz]

Maybe something like:

#include <Excel.au3>
Local $aSentence[3] = ["I'd like this sentence to be red", "I'd like this sentence to be black", "I'd like this sentence to be red again"]
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, 1, $aSentence[0] & @LF & $aSentence[1] & @LF & $aSentence[2], "A1")
Local $oCell = $oWorkbook.Worksheets(1).Range("A1")
;~ $oCell.Characters(Start Character Index, Number of Characters from Start Character Index)
$oCell.Characters(1,Stringlen($aSentence[0]) + 1).Font.Color = -16776961
$oCell.Characters(StringLen($aSentence[0] & @LF),Stringlen($aSentence[1]) + 1).Font.Color = 0x000000
$oCell.Characters(StringLen($aSentence[0] & @LF & $aSentence[1] & @LF),Stringlen($aSentence[2]) + 1).Font.Color = -16776961

 

[siawpo]

Thank you so much Subz. I really appreciate it. 

But it seems like only in the fixed cell. 

My script will search for the cell which containing a specific keyword, maybe it will land on "O36". Then I will need to write the sentence in "O36".

How can I use _Excel_RangeWrite with current ActiveCell?

 

 

[Subz]

You can use _Excel_RangeFind to find all instances, basic example:

#include <Array.au3>
#include <Excel.au3>
Local $aSentence[3] = ["I'd like this sentence to be red", "I'd like this sentence to be black", "I'd like this sentence to be red again"]
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

;~ Begin Creating Dummy Data for Example
For $i = 2 To 15 Step 2
    _Excel_RangeWrite($oWorkbook, 1, "Some dummy data" & @LF & "More dummy data" & @LF & "End dummy data.", _Excel_ColumnToLetter($i-1) & Floor(Random(1,4)))
    _Excel_RangeWrite($oWorkbook, 1, $aSentence[0] & @LF & $aSentence[1] & @LF & $aSentence[2], _Excel_ColumnToLetter($i) & Floor(Random(1,4)))
Next
;~ End Creating Dummy Data

;~ Find all text items named "I'd like this sentence to be red"
Local $aFindAll = _Excel_RangeFind($oWorkbook, $aSentence[0])
    If @error Then Exit MsgBox(4096, "Search Error", '"' & $aSentence[0] & '" not found.')

;~ Loop through the search results and change the color text
For $i = 0 To UBound($aFindAll) - 1
    Local $oCell = $oWorkbook.Worksheets(1).Range($aFindAll[$i][2])
    ;~ $oCell.Characters(Start Character Index, Number of Characters from Start Character Index)
    $oCell.Characters(1,Stringlen($aSentence[0]) + 1).Font.Color = -16776961
    $oCell.Characters(StringLen($aSentence[0] & @LF),Stringlen($aSentence[1]) + 1).Font.Color = 0x000000
    $oCell.Characters(StringLen($aSentence[0] & @LF & $aSentence[1] & @LF),Stringlen($aSentence[2]) + 1).Font.Color = -16776961
Next