[AccessD] Help with creating an Excel Conditional Format from within Access

I finally figured it out!

I found a link with Excel Constants:

> ' Excel Constants
> Public Const xlLeft As Integer = -4131
> Public Const xlThin As Integer = 1
> Public Const xlCenter As Integer = -4108
> Public Const xlBottom As Integer = -4107
> Public Const xlTop As Integer = -4160
> Public Const xlRight As Integer = -4152
> Public Const xlAutomatic As Integer = -4105
> Public Const xlSolid As Integer = 1
> Public Const xlMedium As Integer = -4138
> Public Const xlDouble As Integer = -4119
> Public Const xlThick As Integer = 4
> Public Const xlEdgeBottom As Integer = 9
> Public Const xlDatabase As Integer = 1
> Public Const xlPageField As Integer = 3
> Public Const xlColumnField As Integer = 2
> Public Const xlDataField As Integer = 4
> Public Const xlRowField As Integer = 1
> Public Const xlEdgeRight As Integer = 10
> Public Const xlDown As Integer = -4121
> Public Const xlToLeft As Integer = -4159
> Public Const xlToRight As Integer = -4161
> Public Const xlCellValue As Integer = 1
> Public Const xlLess As Integer = 6
> Public Const xlGreater As Integer = 5
> Public Const xlGreaterEqual As Integer = 7
> Public Const xlBetween As Integer = 1
> Public Const xlMaximized As Integer = -4137- Hide quoted text -
> Public xlThemeColorAccent2 As Integer =6

I replaced the constants with the actual integer values and it now works!

Thanks everybody,
David

On Thu, Feb 2, 2012 at 12:19 PM, David McAfee <davidmcafee at gmail.com> wrote:

> The export works great, I'm having trouble with the conditional formatting:
>
>         With .Worksheets("R6Payouts").Columns("M:M")
>             .Select
>
> '*****************Causes error
> 'Line below , is the original line, gets a compile error
> 'Access VBA Compiler doesn't like xlCellValueand xlLess
>             .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
> Formula1:="=0"
> 'If I put quotes around the values, I get runtime err 13, Type mismatch:
>            .FormatConditions.Add Type:="xlCellValue", Operator:="xlLess",
> Formula1:="=0"
> 'I tried brackets, but that gives me err 2465 can't find field '|' in my
> expression
>             '.FormatConditions.Add Type:=([xlCellValue]),
> Operator:=(objXL.[xlLess]), Formula1:="=0"
>             .FormatConditions(.FormatConditions.Count).SetFirstPriority
> '********************************
>             With .FormatConditions(1).Font
>                 .Color = -16777024
>                 .TintAndShade = 0
>             End With
>             With .FormatConditions(1).Interior
>                 .PatternColorIndex = "xlAutomatic"
>                 .ThemeColor = "xlThemeColorAccent2"
>                 .TintAndShade = 0.799981688894314
>             End With
>             .FormatConditions(1).StopIfTrue = True
>         End With
>
>
>
>
> Any ideas?
>
> TIA
> David
>
> From: David McAfee <davidmcafee at GMAIL.COM>
>>
>
>
>> Cross posted, sorry to those of you on both lists.
>>
>>
>>
>> I have this stored procedure that I export into an excel sheet.
>> I'd like to add two conditional formats that highlight column M (light)
>> red
>> (pink?) if the value is <0.
>> The other format would color column N Yellow if the value >.2 (20%)
>>
>> I could do the coloring, but the user wants the conditional formatting
>> instead in case they play with the sheet's numbers.
>>
>> Does anyone know how to do this off the top of their head?
>>
>
>