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

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