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

Hi David

I would be careful about applying a CF to the entire column, there are limits regarding CF including to how many cells you can apply CF to in Excel before it bombs out.

For example <<http://support.microsoft.com/kb/215783>> - this limit maybe increased in XL2007+, but it may not be too.
You would be better off finding the known range of data in that column and only applying the CF to that.  If it is a case where the user is going to add to the data, find the last row and then add "X" amount too it.

Anyway, hope that helps a bit.

Cheers
Darryl


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Friday, 3 February 2012 10:04 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com