David McAfee
davidmcafee at gmail.com
Thu Feb 2 17:52:01 CST 2012
Thanks for the tip.
I modified:
'Add conditional Formatting to Column M
With .Worksheets("R6Payouts").Columns("M:M")
to:
'Add conditional Formatting to Column M
'With .Worksheets("R6Payouts").Columns("M:M")
row = .CountA(.Worksheets("R6Payouts").Range("A:A"))
With .Worksheets("R6Payouts").Range(.Cells(2, 13), .Cells(row, 13))
On Thu, Feb 2, 2012 at 3:35 PM, Darryl Collins <
darryl at whittleconsulting.com.au> wrote:
> 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
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>