Complex criteria for Excel conditonal formatting

I'm exporting an Access report to Excel, and want to include the conditional formatting via VBA.  The number of rows will vary depending on the underlying Access report criteria, so I've been using the .EntireColumn("B:B") syntax for most of my formatting efforts.  But now I have a problem.

The color coding for column E depends on both the value in columns C and E

    C             E            background Format
 <250      <250          normal (this is the base case)
 <250    >=250          light green
>=250     <250          light red

using VBA, how do I right the criteria expression so that I can do all of the conditional formatting for column E at one time, and don't have to do it one cell at a time.
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

chwong67Commented:
Try Macro below
Sub ChangeColor()
For Each cel In Range("E2", Cells(Rows.Count, "E").End(xlUp))
    If cel.Offset(0, -2).Value < 250 And cel.Value >= 250 Then
       cel.Interior.ColorIndex = 35 'Light Green
    ElseIf cel.Offset(0, -2).Value >= 250 And cel.Value < 250 Then
       cel.Interior.ColorIndex = 38 'Light red
    Else
       cel.Interior.Pattern = xlNone
    End If
Next cel


End Sub

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
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
chwong67,

Worked like a charm, although those colors are a little "fufu" for me.  Don't suppose the colorindex property will accept an RGB value.  Is there a backgroundcolor or some other property that would accept the full range of rgb values?
0
chwong67Commented:
I used 'Record Macro' Function to determine the color
cel.Interior.Color = 5296274 'Light Green
cel.Interior.Color = 255 'Red
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.

Patrick MatthewsCommented:
fyed,

I thought you wanted a Conditional Formatting solution; the code above is not doing Conditional Formatting :)

Any, if you are still interested...



With xlWorksheet
    .Range("e1").Select
    .Cells.FormatConditions.Delete
    With .Range("e:e")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(C1<250,E1>=250)"
        .FormatConditions(1).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = True
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(C1>=250,E1<250)"
        With .FormatConditions(2).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.399945066682943
        End With
        .FormatConditions(2).StopIfTrue = True
    End With
End With

Open in new window



Go ahead and play with the Color / ThemeColor / TintAndShade to determine the values you want.

Please note that this is also an exception to the rule that you don't have to actually select anything, as described here:

http://www.experts-exchange.com/blogs/matthewspatrick/B_2912-You-never-have-to-select-a-range-in-Excel-VBA-except-when-you-do-have-to.html

Patrick
0
Patrick MatthewsCommented:
fyed,

Also, rather than setting this every time you do the export, I would use an Excel template file for the export, and simply build the Conditional Formatting into the template.

Patrick
0
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Patrick,

Asked about conditional formatting, but realized after I did so, that since the spreadsheet is locked from editing, the code that chwong67 provided would meet my needs.  If chwong67 doesn't mind, I'll ask the administrators to reopen the question and will split the points.

Your formula expressions were the piece I was missing from my earlier attempts at conditional formatting.  I tried a variety of things via recorded macros, and could never figure out how to implement more than a single criteria. Thanks for that bit.

The template idea is tempting, but with over 20 reports to maintain (all have to be exportable to Excel, most require column filtering and some other formatting) it seems simpler to maintain by just writing the code to do the work at runtime.  That way, I don't have to worry about whether everyone using the application has all of the templates and the most recent versions.
0
Patrick MatthewsCommented:
fyed,

You could put the Excel templates on a network drive.  That eliminates the need to make sure each user has his/her own copy.

Even if that's not feasible, you could use the same mechanism you use to keep the Access front-end refreshed to ensure users have the necessary templates.

:)

Patrick
0
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Patrick,

Thought about the network drive and the "auto-update" aspect of this as well.  Would significantly reduce the amount of code I've had to write.  Started out formatting each report/field separately.  Then as the number of reports that they wanted to do this with grew, wrote a number of subroutines that consolidated multiple steps.

On the other hand, writing all this code to automate Excel really is helping me to expand my knowledge of VBA from just Access to include Excel, expanding my marketability.

Dale
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.