Conditional Formatting for Excel from Access VBA

Experts. I have a routine that dumps data to an excel file. From there I am formatting the excel file. One piece of my formatting is going to be to apply conditional formatting on entire columns based on the value in a different column:corresponding row.

In my attached excel document. Column E has the cell that I want filled in red. Column F contains the value to check if true. If F:x value is "yes" I want to fill in E:x red. Below is the code I have so far. It's erroring out on the formatconditions line.

    Application.SetOption "Show Status Bar", True

    Dim xlApp As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
    With xlApp
        xlApp.Visible = True
            .Application.Sheets("qryDPCAuditReport").Select
            .Application.Cells.Select
            .Application.Rows(1).Select
            .Application.Selection.Font.Bold = True
            .Application.Range("E2").Select
            With E2
            E2.FormatConditions.Add Type:=xlExpression, Formula1:="($F2=yes)"
            .Application.Selection.Interior.Color = rgbRed
            End With

    xlApp.Visible = True
            
    'Save
    .Application.ActiveWorkbook.Save
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing

Open in new window


Thank you!
DPCAudit.xls
acramer_dominiumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
No Excel expert, but I would think it would be as easy as selecting the entire E column
Then in the Conditional formatting box enter:
CF
Sadly, this does not work for me, ...

So lets see what another Expert may post.
Perhaps we can both learn something...
;-)

JeffCoachman
0
acramer_dominiumAuthor Commented:
I am able to apply the formatting while in excel. What I need to do is do it all behind the scences in Access in VBA. What I'm being asked for is for the user to click a button I dump the report to excel, format the report in excel from the access appliacation and apply shading where needed from the access application.
0
Jeffrey CoachmanMIS LiasonCommented:
Ok, I could show you how to do the formatting in Access (Without going to Excel), ...but if it needs to be in Excel, let's wait for an Excel Expert to chime in...

JeffCoachman
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

IrogSintaCommented:
Here's a revision to your code:
Const xlExpression = 2
    Dim xlApp As Object
    Dim xlSheet As Object
    Dim xlRng As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets("qryDPCAuditReport")
    Set xlRng = xlSheet.range("E:E")
    
    xlApp.Visible = True
    xlSheet.Select
    xlSheet.Rows(1).Font.Bold = True
    xlRng.FormatConditions.Add Type:=xlExpression, Formula1:="=($F1=""yes"")"
    xlRng.FormatConditions(1).Interior.Color = vbRed
                                                            
    'Save
    xlApp.activeworkbook.Save
    xlApp.Quit
    Set xlApp = Nothing
    Set xlSheet = Nothing

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FaustulusCommented:
Your problem appears to have two facades. One is the action of applying the formatting. The other is to call the program. This code applies the formatting:-
Option Explicit

Sub ApplyCellFormat()

    Const SheetName As String = "qryDPCAuditReport"
    
    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim Rng As Range
    
    Set Wb = ActiveWorkbook
    Set Ws = Wb.Sheets(SheetName)
    Set Rng = Ws.Columns(5)
    
    With Rng.FormatConditions
        .Delete
        .Add Type:=xlExpression, _
             Formula1:="=$F1=""yes"""
        With .Item(1)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            .StopIfTrue = True
        End With
    End With
End Sub

Open in new window

You can paste this procedure into a standard code module in the workbook containing your data. By default the name will be 'Module1', and you need to create it. Done't use any of the existing code modules. You can run the procedure from the Macros list in the Developer tab or directly from the VB Editor.

I have designed this code a little more elaborately than required. For one, you will find your way through it easier than through a more compact syntax. The other reason is that it isn't clear how you would like to call this code eventually. Perhaps the best way would be to have a template with the code in it which you use to dump the data in. However, if Access doesn't allow you the choice of a template and creates a new workbook of its own there are other ways, such as creating an add-in. In order to make a suggestion more should be known of your workflow and preferences.
0
acramer_dominiumAuthor Commented:
That worked. That dumped data to my spreadsheet and applied cell shading where needed. Thank you!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.