How can I do Excel conditional formatting through VFP automation?

[mkrausnick]

I want to shade alternate rows of a specific range in an Excel spreadsheet that I populate through VFP automation.

In Excel directly I would highlight the range, choose Conditional Formatting from the Format menu, then choose 'Formula is' and enter =MOD(R0W(),2)=0 in the formula field.

How can I do this via automation?

Thanks!

Mike Krausnick
Dublin, California

 

[JRB-Bldr]

That is the 'standard' way of approaching this type of issue is to Record a Macro while doing the work "by hand" in Excel.

Then you can examine the Macro code to see what steps were executed.

Then merely write comparable code using VFP Automation.

Good Luck,
JRB-Bldr

 

[TamarGranor]

I'm pretty sure you'll want to work with the FormatConditions collection.

Tamar

 

[JRB-Bldr]

I recorded a simplistic Macro by doing the work "by hand" in Excel

* --- Excel Macro VBA Code ---
Range("D5").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(R0W(),2)=0"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With

You should be able to do the same with the specifics that you want.

Now convert it to VFP Office Automation code.

Depending on how complex you want your ConditionalFormating to be I'd suggest that you do a Google search for:
"FormatConditions" VFP
I found a number of search result examples.

Good Luck,
JRB-Bldr

 

[mkrausnick]

Thanks all for the tips. Here's what finally worked:

WITH oExcel.ActiveWorkBook.ActiveSheet.Range("A4:"+cEndCell)
    .FormatConditions.Delete()
    .FormatConditions.Add(2,,"=mod(row(),2)=1")
    .FormatConditions(1).Interior.ColorIndex = 15
ENDWITH

Note the two commas in the Add() parameter list. It took me awhile to figure that out.

Mike Krausnick
Dublin, California