Adding a conditional format to an excel report via vbscript

[Gilliland, Gill]

I've found plenty of references to add a conditional format to an excel
sheet using VBA, but I cannot figure out how to translate that to vbscript.

The following is a VBA example that would alternate row colors. I would like
to set this formatcondition using vbscript:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 15

Thanks for any help,
Gill

[J.Williams]

With VBScript, where you have named arguments like Type:=, you have to drop
the names and place the arguments in the order expected by the method.

For example, the FormatConditions Add method has the following arguments:

expression.Add(Type, Operator, Formula1, Formula2)

With Excel VBA, if you use named arguments you can specify these arguments
in any order and you can also omit optional arguments. With VBScript
however, you can't use named arguments and therefore must specify them in
the correct order and use commas to denote missing optional arguments.

Try this:

'Excel 2003 constants from
http://msdn2.microsoft.com/en-us/library/aa221100(office.11).aspx

const xlExpression = 2

Set oExcel = CreateObject("Excel.Application")
Set oWks = oExcel.Workbooks.Add()

oExcel.Range("A1:D20").Select
Set oSelection = oExcel.Selection

oSelection.FormatConditions.Delete

'2nd argument (Operator) of Add method is ignored if 1st argument (Type) is
xlExpression

oSelection.FormatConditions.Add xlExpression, , "=MOD(ROW(),2)=1"
oSelection.FormatConditions(1).Interior.ColorIndex = 15

oExcel.ActiveWorkbook.SaveAs "test.xls"
oExcel.ActiveWorkbook.Close(0)
oExcel.Quit

"Gilliland, Gill" <Gill.Gi...@UnitedWater.com> wrote in message
news:C2D4C350.4261%Gill.Gi...@UnitedWater.com...

[Gilliland, Gill]

Absolutely perfect! Thanks for the explanation and web reference!

Gill

On 7/31/07 8:31 PM, in article f8ok4v$hgq$1...@news.freedom2surf.net,

[angelo tavola]