Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Formula propogating incorrectly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Formula propogating incorrectly
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Formula propogating incorrectly
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 "Jim Cone" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula incorrectly returns zero | Excel Discussion (Misc queries) | |||
Cell Formatting Incorrectly Displayed | Excel Discussion (Misc queries) | |||
VBA Code incorrectly formatting data | Excel Discussion (Misc queries) | |||
How to get Excel to stop formatting time cells incorrectly | Excel Discussion (Misc queries) | |||
Formula counts incorrectly | Excel Discussion (Misc queries) |