Using Access VBA to Format Conditionally in Excel

[laurajayne.cozens]

Here's a problem I have been stuck on for the last three months.

I have a lot of VBA code in an access module that creates queries and
tables. At the end of the code, I transfer all this information to an
excel spreadsheet using the transfer spreadsheet option.

I then have a batch of code which opens the spreadsheet, formats it,
saves it and closes it. It works like a dream.

However, the code is currently telling the excel spreadsheet to select
collumns K and L and turn the whole columns pink. What I actually
want is the code to search through columns K and L and turn an
individual cell pink if the value is NO.

Does this mean I have loop around each cell? How do I do this - i
cant find any otherexamples on the net!!
Any help or suggestions greatly appreciated.

Here's the current code:

Dim xlApp As Object

Dim StrPath As String

StrPath = "H:\Census_Checking\FSM_TO_BE_CHECKED.xls"

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
..Workbooks.Open Filename:=StrPath
..Columns("K:L").Select
With .Selection
..Interior.ColorIndex = 7
End With
End With
With xlApp
..Cells.Select
..Cells.EntireColumn.AutoFit
..Rows("1:1").Select
..Selection.Font.Bold = True
End With
xlApp.Application.ActiveWorkbook.Save
xlApp.Quit

 

[Roger Carlson]

Why not use Excel Conditional Formating to format the columns you want, then
save this file as a template. (.XLT) Then instead of creating a new file,
open the template (which will create a new file, but with the formatting
already there).

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb" which does something like this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[MH]

You would get better answers if you posted in microsoft.public.excel.misc

MH

 

[Dale Fye]

Laura,

Recommend you post this to an Excel programming group.

Dale

 

[laurajayne.cozens]

I have tried posting this message in other excel groups and all I get
back are excel answers! I know how to solve this problem in Excel -
but how do I do it from an Access Query? hence, the reason why I
posted this query in here.

Roger, thanks for your help. I will have a look at your website and
try using a template.

laura