Conditional Formatting for Excel from Access VBA

Posted on 2013-09-24
Medium Priority
Last Modified: 2013-09-30
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.Selection.Font.Bold = True
            With E2
            E2.FormatConditions.Add Type:=xlExpression, Formula1:="($F2=yes)"
            .Application.Selection.Interior.Color = rgbRed
            End With

    xlApp.Visible = True
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing

Open in new window

Thank you!
Question by:acramer_dominium
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39519192
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:
Sadly, this does not work for me, ...

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


Author Comment

ID: 39519202
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39519646
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...

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 39527036
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.Rows(1).Font.Bold = True
    xlRng.FormatConditions.Add Type:=xlExpression, Formula1:="=($F1=""yes"")"
    xlRng.FormatConditions(1).Interior.Color = vbRed
    Set xlApp = Nothing
    Set xlSheet = Nothing

Open in new window

LVL 14

Expert Comment

ID: 39527107
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
        .Add Type:=xlExpression, _
        With .Item(1)
            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.

Author Closing Comment

ID: 39533667
That worked. That dumped data to my spreadsheet and applied cell shading where needed. Thank you!!!

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Though there are a few manual ways to import PST files to Office 365 , third-party PST to Office 365 import tools are preferred over them due to various reasons.  Consequently, many tools or services are available for the same. Here, we pick the to…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question