Conditional Formatting Formula propogating incorrectly

[scottydel]

Hello,

I'm using Excel/Access 2003. Form with Access VBA I am modifying an Excel
file I have created. The following code is behaving strangely:

With xlsCurrentWorksheet.Range("AA11:AC" & intRecords + 10)
.ClearContents
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,
Formula1:="=X11"
.FormatConditions(1).Interior.ColorIndex = 5
.FormatConditions(1).Interior.Pattern = xlLightUp
End With

The code should take a range in question, say "AA11:AC99", and use
conditional formatting based on the contents of cells X11:X99. So if X11="",
AA11 should NOT have formatting. And if X11 <> "" AA11 SHOULD have
formatting. If cell Y11 is blank, cell AB11 should NOT have formatting, and
so on.

This code works sometimes, and then doesn't work somtimes.

When it doesn't work, I look up the Conditional Formatting in the resulting
worksheet, and instead of cell AA11 looking at cell X11 (as the Formula1
property would indicate), cell AA11 is looking at cell AW11. And cell AB11
is looking at cell AX11, and so on. So it seems to have used the wrong
initial cell to populate the conditional formatting formula.

Any ideas?

Any help is appreciated.

Thanks,

-Scott

 

[Jim Cone]

Scott,
The CF formula is dependent on the location of the active cell relative
to the specified cells.
( I believe the "summer intern" in the Excel group at MS got the CF code
assignment and here we are.) <g>
Try selecting the first cell in the range before the CF code executes.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"scottydel" <[email protected]>
wrote in message
Hello,
I'm using Excel/Access 2003. Form with Access VBA I am modifying an Excel
file I have created. The following code is behaving strangely:

With xlsCurrentWorksheet.Range("AA11:AC" & intRecords + 10)
.ClearContents
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,
Formula1:="=X11"
.FormatConditions(1).Interior.ColorIndex = 5
.FormatConditions(1).Interior.Pattern = xlLightUp
End With

The code should take a range in question, say "AA11:AC99", and use
conditional formatting based on the contents of cells X11:X99. So if X11="",
AA11 should NOT have formatting. And if X11 <> "" AA11 SHOULD have
formatting. If cell Y11 is blank, cell AB11 should NOT have formatting, and
so on.

This code works sometimes, and then doesn't work somtimes.

When it doesn't work, I look up the Conditional Formatting in the resulting
worksheet, and instead of cell AA11 looking at cell X11 (as the Formula1
property would indicate), cell AA11 is looking at cell AW11. And cell AB11
is looking at cell AX11, and so on. So it seems to have used the wrong
initial cell to populate the conditional formatting formula.
Any ideas?
Any help is appreciated.
Thanks,
-Scott

 

[scottydel]

Jim,

That's the answer I was looking for. I figured it was something buggy like
that. Thank you for the insight! Agreed on the interns...

-Scott