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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I used 'Record Macro' Function to determine the color
cel.Interior.Color = 5296274 'Light Green
cel.Interior.Color = 255 'Red
cel.Interior.Color = 5296274 'Light Green
cel.Interior.Color = 255 'Red
fyed,
I thought you wanted a Conditional Formatting solution; the code above is not doing Conditional Formatting :)
Any, if you are still interested...
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:
https://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
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 WithGo 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:
https://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
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
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
ASKER
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.
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.
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
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
ASKER
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
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
ASKER
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?