excel condition problem

[faustf]

hi guys

 i have a script , i want  set  the colum E  with condition , if in colum  have  1to 10   color row   red  if  0 to -10  green  i try to do  with this script   but return me  this error

XCEL_CREA()
"C:\Users\SviluppoGest\Desktop\dacancellare\exce.au3" (48) : ==> The requested action with this object has failed.:
With $oWorkbook.Selection.FormatConditions(1)
With $oWorkbook^ ERROR

 

expandcollapse popup

#include-once
#include <Excel.au3>
#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>

Global $xlCellValue1 = 1, $xlBetween1 = 1

_EXCEL_CREA()

Func _EXCEL_CREA()
    ConsoleWrite('@@ (693) :(' & @MIN & ':' & @SEC & ') _EXCEL_CREA()' & @CR) ;### Function Trace

    Local $oAppl = _Excel_Open(True, False, True, True, True)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    $oWorkbook = _Excel_BookNew($oAppl)

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "VIA", "A1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "MQ", "B1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "PREZZO", "C1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "PREZZO al MQ", "D1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "%SCOSTAMENTO", "E1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "LINK", "F1")
    $oWorkbook.ActiveSheet.Range("A1:F1").Interior.ColorIndex = 56
    $oWorkbook.ActiveSheet.Range("A1:F1").Font.Bold = True
    $oWorkbook.ActiveSheet.Range("A1:F1").Font.ColorIndex = 2
    ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Disponibilita", "G1")
    ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Arrivi", "H1")
    ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Listino", "I1")
    ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Margine", "J1")
    ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Prezzo c&c", "K1")
    ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Prezzo Web", "L1")
    ;#cs
    With $oWorkbook.activesheet
        .Range("E2:E1000").Select ;Select A1:A10
        .Selection.FormatConditions.Delete ;Delete Existing FormatConditions
        .Selection.FormatConditions.Add($xlCellValue1, $xlBetween1, "1", "10") ;FormatConditions(1)
        .Selection.FormatConditions.Add($xlCellValue1, $xlBetween1, "0", "-10") ;FormatConditions(2)
    EndWith

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

    With $oWorkbook.Selection.FormatConditions(2)
        .Font.Bold = False
        .Font.Italic = True
        .Font.ColorIndex = 10 ;Green
    EndWith
    ;#ce
    Return $oWorkbook
EndFunc   ;==>_EXCEL_CREA

anyone  have some suggestions ???

thankz

 

[Nine]

Try this  (tested and working) :

Local $oRange = $oWorkbook.activesheet.Range("E2:E20")
    With $oRange
        .FormatConditions.Delete ;Delete Existing FormatConditions
        .FormatConditions.Add($xlCellValue1, $xlBetween1, "1", "10") ;FormatConditions(1)
        .FormatConditions.Add($xlCellValue1, $xlBetween1, "0", "-10") ;FormatConditions(2)
    EndWith

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

    With $orange.FormatConditions(2)
        .Font.Bold = False
        .Font.Italic = True
        .Font.ColorIndex = 10 ;Green
    EndWith

[faustf]

it  work thankz  so much :)