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

I get a "user defined type not defined" error on the first line.

This still used xlCellValue which is causing the error in Access.

I recorded a macro in Excel2007 and brought the code into Access.


On Thu, Feb 2, 2012 at 1:15 PM, Mark Simms <marksimms at verizon.net> wrote:

> This test fine: Excel 2007.
> Watch your constants and your nested WITH's. I try to minimize those...
> Also, watch to properly maintain your format conditions for a range....
> Hint: You might have to delete them before adding new ones.
>
> Private Sub TestFormatConditions()
>
>  Dim oRng As Excel.Range
>  Dim oFmt As Excel.FormatCondition
>
>  Set oRng = ActiveSheet.Columns("G").EntireColumn
>  Set oRng = oRng.Resize(15).Offset(1)
>  ' change the colors if the value is over 100
>  Set oFmt = oRng.FormatConditions.Add(xlCellValue, xlGreater, 100)
>  With oFmt.Font
>     .Color = -16777024
>    .TintAndShade = 0
>  End With
>   With oFmt.Interior
>       .PatternColorIndex = xlAutomatic
>      .ThemeColor = xlThemeColorAccent2
>      .TintAndShade = 0.799981688894314
>  End With
>   oFmt.StopIfTrue = True
>
>
> End Sub
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> > bounces at databaseadvisors.com] On Behalf Of David McAfee
> > Sent: Thursday, February 02, 2012 3:19 PM
> > To: Access Developers discussion and problem solving; ACCESS-
> > L at peach.ease.lsoft.com; Microsoft Excel Developers List
> > Subject: Re: [AccessD] Help with creating an Excel Conditional Format
> > from within Access
> >
> > 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?
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>