VBS and Excel (Conditional Formatting)

Hi

I can use VBS to do most things in Excel (copy/paste/create
spreadsheets etc), but I cannot get Conditional Formatting to work.
I've googled and haven't came up with anything that relates to VBS,
only VBA.

The code that Excel makes up from a Macro is:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="80"
Selection.FormatConditions(1).Font.ColorIndex = 3

VBS accepts the .Delete line, but if I try to add one, it comes up with
errors using the above code.

IE The below works
Set objXL = WScript.CreateObject("Excel.Application")
objxl.Selection.FormatConditions.Delete

But objXL.Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:="80" doesn't.

Does anyone know what syntax I should use to get it to work?

Cheers

Leon

axtens
Thu Dec 29 20:35:51 CST 2005

Presupposing that you have defined the following constants:

const xlCellValue = 1
const xlGreater = 5

then the syntax is positional:

objXL.Selection.FormatConditions.Add xlCellValue, xlGreater, "80"

Regards,
Bruce.

leonvd
Thu Dec 29 21:03:36 CST 2005

Many thanks! That worked.

Any idea where I can find out all those Constants?

Leon

axtens
Thu Dec 29 22:46:50 CST 2005

Tools>Macro>Visual Basic Editor
Click on the Object Browser button
Search for the name of the constant (or the function or whatever)
F1 when you find it for more information

Likely there's a .hlp or a .chm on this box somewhere that has all the
same stuff in it.

Regards,
Bruce.