Formatting Excel from an ACCESS MODULE

[laurajayne.cozens]

I ahve tried posting this in the Access Module forum buit they told me
to try here. Please note that I writing the VB code in an ACCESS
Module not directly in EXCEL... Thanks in advance for your help!

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

 

[MDW]

Try replacing this:

With xlApp
...Workbooks.Open Filename:=StrPath
...Columns("K:L").Select
With .Selection
...Interior.ColorIndex = 7
End With
End With
With xlApp

with this:

Set R = xlApp.Worksteets(1).Range("K1:L999")
For Each C In R.Cells

If C.Value = "NO" Then
C.Interior.ColorIndex = 7
End If

Next
Set objR = Nothing

A few things to note:
1) I assumed the Excel file you're working with only has one sheet. If it
has more than one, your best bet is to change 'Worksteets(1)' with
'Worksheets("Sheet2")', using whatever the name of the sheet is
2) I also assumed that the area you'll need to check goes down to row 999.
You may need to adjust that. If the size of the range you're going to search
is extremely variable, there are ways you can dymatically change how far down
the search goes.

HTH

 

[MDW]

SORRY! Slight bug in the code I gave you.

Set objWB = xlApp.Workbooks.Open(StrPath)
Set R = objWB.Worksteets(1).Range("K1:L999")
For Each C In R.Cells

If C.Value = "NO" Then
C.Interior.ColorIndex = 7
End If

Next
Set objR = Nothing
Set objWB = Nothing

 

[urkec]

Sub PinkIt()

For Each c In Sheets(1).UsedRange.Cells
If c.Value = "NO" Then
c.Interior.ColorIndex = 26
End If
Next

End Sub