Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2007 MS Access VBA to Excel - Conditional Formatting Anomaly

    Hello all,
    So I have this MS Access VBA code that uses automation to create/build an Excel spreadsheet. I use VBA to create some conditional formatting in Excel. Before I create the conditional formatting I activate the first cell of the range to which the conditional formatting will apply. The code looks something like this (excerpt):

    .Cells.FormatConditions.Delete
    Set graph_range = .Range("K8:EA1000")
    .Range(graph_range).Cells(1, 1).Activate
    With graph_range
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(AND(K$8>=$F8,K$8<=$G8,$GB8=8),1,0)"
    .FormatConditions(graph_range.FormatConditions.Cou nt).SetFirstPriority
    With .FormatConditions(1)
    With .Borders(xlTop)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With .Borders(xlBottom)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With .Interior
    .PatternColorIndex = xlAutomatic
    .Color = 16776960
    .TintAndShade = 0
    End With
    .StopIfTrue = True
    End With

    Here's my problem: notice the Formula1 part... it reads "=IF(AND(K$8>=$F8,K$8<=$G8,$GB8=8),1,0)". But... every single time no matter what I do... the actual conditional formatting formula ends up reading "=IF(AND(XAX$8>=$F6,XAX$8<=$G6,$GB6=8),1,0)". Notice that the relative portions of the formula always change to some incorrect column or row. The actual formatting works... if the formula would come in correctly.

    How do I get it to be entered correctly?

    Thanks for any suggestions.

    Steve

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK... I finally figured it out! Ugh... most times it's the simplest mistakes.

    The problem was in my Range activation statement. Apparently I had to specifically call out the address. For instance... instead of ".Range(graph_range).Cells(1, 1).Activate" I needed to use ".Range(graph_range.Address).Cells(1, 1).Activate" instead.

    All is right with world again. ;-)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •