Excel Conditional Formatting

HCL Domino Domino Forum
1

I am attempting to convert the vb script code for conditional formatting from excel to lotusscript.

This is the vbscript (from macro in Excel):

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

    "=""mod=(row(),2)=0"""

I’ve tried several variations of this code in my lotusscript. What I have currently is (which isn’t working):

xlApp.Selection.FormatConditions.Add

xlApp.Selection.FormatConditions.Add.Type = xlExpression

xlApp.Selection.FormatConditions.Add.Formula1 = "=MOD(ROW(),2)=1"

The error I’m getting is "Wrong number of arguments for automation object so I’m guessing the Type and Formula have to be entered differently.

Can anyone assist?

thanks

anne

2

Subject: Excel Conditional Formatting

I don’t think LotusScript can handle passing call parameters into modules by name - you’ll have to use the By Position alternative.

Set format_range = Selection.FormatConditions

Call format_range.Add (xlExpression, xlEqual , “=”“mod=(row(),2)=0"”" )

Also, I don’t think you’ll be able to use the Excel constants, so you’ll need to look up their real values.

Set format_range = Selection.FormatConditions

Call format_range.Add (2, 3 , “=”“mod=(row(),2)=0"”" )

And I think the actual condition specified should be corrected to …

Set format_range = Selection.FormatConditions

Call format_range.Add (2, 3 , “=mod(row(),2)=0” )

3

Subject: IT WORKS! Excel Conditional Formatting

Thanks Graham. That code works.

Here is what I have, for anyone searching later:

.

.

.

'adding alternating row colors

Const xlExpression = 2

Const xlEqual = 3

xlApp.Cells.Select

Set format_range = xlApp.Selection.FormatConditions

Call format_range.Add (xlExpression, xlEqual , “=mod(row(),2)=0” )

xlApp.Selection.FormatConditions(1).Interior.ColorIndex = 15

.

.

.

Thanks

anne

4

Subject: RE: IT WORKS! Excel Conditional Formatting

I am working with Excel 2007 and finaly the code I have made is:

Set excelApp = CreateObject(“Excel.Application”)

Set excelNewWkbk = excelApp.Workbooks.Add

Set excelSheet = excelNewWkbk.ActiveSheet

Dim formatRange As Variant

Set eRange=excelSheet.Range(excelSheet.Cells(EndTitleRow+1, 1),excelSheet.Cells(UsedRows-1, usedColumns))



With eRange

	.FormatConditions.Delete

	.FormatConditions.Add 2, 3 , (|=mod(row();2)=0|)

	.FormatConditions(1).Interior.PatternColorIndex = -4105

	.FormatConditions(1).Interior.ThemeColor = 1

	.FormatConditions(1).Interior.TintAndShade = -0.0499893185216834 'gray 5%

End With

The difference from the code above is that only third parameter is in brackets. And the formula is with semicolon instead with comma.

5

Subject: RE: IT WORKS! Excel Conditional Formatting

It am verry sorry but this doesn’t works for me. I still get OLE automation Error on .Add row …Is there something special in declaring xlApp or may be something else?

6

Subject: Excel Conditional Formatting

Have you tried just using curly braces or pipe symbols on the Formula1 piece, i.e.

xlApp.Selection.FormatConditions.Add.Formula1 = {="“mod=(row(),2)=0"”}

The odd configuration of double quotes could be messing up the Lotusscript translation of the value.

I don’t know if this will work, but it’s worth a shot.

7

Subject: RE: Excel Conditional Formatting

Great idea, and it would make sense, but the error is occuring on the first line of the code I posted (the .Add).

I tried

xlApp.Selection.FormatConditions.Add (xlExpression, “=mod(row(),2)=0”)

but I that won’t even let me save the script (illegal use of parentheses)

8

Subject: RE: Excel Conditional Formatting

Use Call :wink: