How to format range of data in Excel workbooks from Access

[lube8]

Hello,

I have some existing excel files that are generated from Access. How would I go about formatting the worksheets of these workbooks based on a string that appears in a row?

For example, I would like to have the range of cells (A:G) for each worksheet in each workbook, highlighted in yellow with top and bottom borders, but only for the rows where the contents in column A contain the string "Hours Variance".

There are several workbooks each containing several tabs and my existing modules have been generated in Access. Thanks.

 

[xlbo]

Presumably you know how to set a reference to excel (if you are already creating workbooks)
The easiest way to add this formatting is to use CONDITIONAL FORMATTING. With a reference to excel set, this should work

For each sht in activeworkbook.worksheets
With sht.Columns("A:G&quot
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A1=""Hours Variance"""
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 36
End With
Next

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[lube8]

My original vba code to transfer data to multiple sheets is a loop (transferspreadsheet procedure within Access module).

Do I need to write code in my Access module to open each excel workbook and apply this formatting to each worksheet? ... Or can this formatting occur without me opening each workbook and be included within my loop for the transferspreadsheet procedure? Thanks.

 

[xlbo]

Please post the code you are using - if you are creating new workbooks each time then yes, you will have to open each one as part of a loop and apply the formatting as per above. If not, you can set this up before hand and you won't need to do it at all....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244