Export Conditional Formatting to Excel

[Reebis]

I have a form that uses conditional formatting to turn the cell either red or
green based on if the line item is ahead or behind schedule. When I go to
export this to excel, the formatting is loast (i.e no red/green in the .xls).
Is there a way to amke the color formatting come thru in the export?

Thanks,

-bob

 

[Dale Fye]

Yes,

After you have exported to Excel, you can open the Excel workbook and use
automation to change your formatting of that column. The way I would do this
is:

1. Export your report to Excel.
2. Open Excel, and record a new macro. Once you have started recording the
new macro:
a. Select the column you want to format.
b. Click on the Formatting - Conditional Formatting menu item.
c. Set your appropriate formatting options
d. Stop recording the macro
3. Open the Excel VB editor, find the macro you just created, and copy the
code for the formatting and paste it into an Access code module. Add some
code to open Excel, open the spreadsheet, and then do the formatting. It
will look something like below (this is from a macro I run):

Public Sub ExcelConditionalFormatting

dim xlObj as object
dim wbk as object

set xlObj = createobject("Excel.Application")
xlObj.visible = true
set wbk = GetObject("FileName.xls")
wbk.visible = true
wbk.sheets(1).activate

Sheets(1).Range("C:C").Select

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=strFormula
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" & Chr$(64 + SelCol) & "3"
With Selection.FormatConditions(2).Font
.Bold = False
.Italic = False
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(2).Interior.ColorIndex = 19
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" & Chr$(64 + SelCol) & "4"
With Selection.FormatConditions(3).Font
.Bold = False
.Italic = False
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(3).Interior.ColorIndex = 44

wbk.close SaveChanges:=True
xlObj.Quit

End Sub

You can do this without making Excel visible if you want, or could keep it
hidden until the formatting is complete, then make it visible. You might
even want to just save the changes (wbk.save) and leave off the close and
quit methods as the last two lines of this subroutine.

HTH
Dale

 

[Reebis]

Awesome! Thanks Dale! Just what I was looking for.