Excel Conditional Formatting

[iamtheky]

The below writes a 2d array to an excel sheet, then colors the font of those items in column B with values less than 60. i have these remaining issues:

1) select the entire row when the condition is met, and color the font of the entire row red

2) select the entire contents of column "B" to be conditionally formatted without having to specify row count

and help would be greatly appreciated

#include<excel.au3>

Local $DaysLeft[5][2] = [["LocoDarwin", 59],["Jon", 200],["big_daddy", 3000],["DaleHolm", -4],["GaryFrost", 50]]


$oExcel = _ExcelBookNew(0) ;Create new book, make it visible
_ExcelWriteSheetFromArray($oExcel, $DaysLeft, 1, 1, 0, 0) ;0-Base Array parameters

Global Const $xlCellValue = 1
Global Const $xlless = 6

With $oExcel
.Range("B1:B150").Select
.Selection.FormatConditions.Delete ;Delete Existing FormatConditions
.Selection.FormatConditions.Add($xlCellValue, $xlLess, "60") ;FormatConditions(1)
EndWith

With $oExcel.Selection.FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Font.ColorIndex = 3 ;Red
EndWith

_ExcelBookSaveAs($oExcel, @ScriptDir & "" & @Year & @MON & @MDAY & "_daysremaining.xls", "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; And finally we close

Edited September 7, 2012 by boththose

[jdelaney]

$oRange = $oExcel.ActiveCell.EntireColumn

.EntireColumn is the way to go.

[iamtheky]

works for the column range. Thanks.

--I have gone another route now that seems to suffice

#include<excel.au3>

Local $DaysLeft[5][2] = [["LocoDarwin", 59],["Jon", 200],["big_daddy", 3000],["DaleHolm", -4],["GaryFrost", 50]]

$oExcel = _ExcelBookNew(0) ;Create new book, make it visible
_ExcelWriteSheetFromArray($oExcel, $DaysLeft, 1, 1, 0, 0) ;0-Base Array parameters

For $i = 1 to 999
$cell = _ExcelReadCell ($oExcel , $i , 2)
If $cell = "" Then ExitLoop
If $cell < "60" Then
$oExcel.range("A" & $i & ":B" & $i).select
$oExcel.selection.Font.ColorIndex = 3
$oExcel.selection.Font.Bold = True
endif
next

_ExcelBookSaveAs($oExcel, @ScriptDir & "" & @Year & @MON & @MDAY & "_daysremaining.xls", "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; And finally we close

Edited September 7, 2012 by boththose