Issue with conditional formatting - Formula changing
 

First let me preface this by stating that I am not an expert.
That out of the way here is my issue, note that everything else in code in
the project works without issue.

Due to project requirements I am developing a workbook in code and returning
the workbook to the user. There is no vba code contained in the workbook.

I am applying conditional formatting to a range of cells...
'Conditional formats for columns
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions.Delete()
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions.Add(Type:=XlFormatConditi onType.xlExpression,
Formula1:="=OR(AND($C8=1,$F8199,$F8<500),And($C8 1,$F8299,$F8<500))")
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions(1).Font.ColorIndex =
XlColorIndex.xlColorIndexAutomatic
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions(1).Interior.ColorIndex = 40

The issue is this...
If I open a workbook and apply the conditions manually it formats correctly.
However when the workbook that is generated through code is opened I find
that the formula has changed from...
Formula1:="=OR(AND($C8=1,F8199,F8<500),And($C81, F8299,F8<500))")

TO
"=OR(AND($C9=1,K9199,K9<500),And($C91,K9299,K9< 500))"

Note that the rows between the ranges are headers for the sections.
No cells are merged and the workbook that is used as a template has no
conditions on the worksheet.

This has been driving me nuts for a week now and I am at my wits end.
One friend stated that it might have to do with non adjacent cells being
selected and the index becomming corrupted.
Any and all help would be GREATLY appreciated.
Thanks in advance.
Environment:
Windows XP Service Pack 3
Excel 2007 all updates and patches applied
..NET 2.0 and 3.5 with service packs applied
Visual Web Developer 2008 Express Edition

Correction: Issue with conditional formatting - Formula changing
 

Correction: in the in line code below Formula 1 is shown incorrectly.
In the current code it is:
Formula1:="=OR(AND($C8=1,F8199,F8<500),And($C81, F8299,F8<500))")

I accidentally copied in the wrong code.

Otherwise the issue is as stated.

"JDinPA17603" wrote:

First let me preface this by stating that I am not an expert.
That out of the way here is my issue, note that everything else in code in
the project works without issue.

Due to project requirements I am developing a workbook in code and returning
the workbook to the user. There is no vba code contained in the workbook.

I am applying conditional formatting to a range of cells...
'Conditional formats for columns
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions.Delete()
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions.Add(Type:=XlFormatConditi onType.xlExpression,
Formula1:="=OR(AND($C8=1,$F8199,$F8<500),And($C8 1,$F8299,$F8<500))")
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions(1).Font.ColorIndex =
XlColorIndex.xlColorIndexAutomatic
.Range("f8:f" & value1 + 4 & ",h8:i" & value1 + 4 & ",f" &
value1 + 6 & ":f" & value2 + 4 & ",h" & value1 + 6 & ":i" & value2 + 4 & ",f"
& value2 + 6 & ":f" & value3 + 4 & ",h" & value2 + 6 & ":i" & value3 + 4 &
",f" & value3 + 6 & ":f" & value4 & ",h" & value3 + 6 & ":i" &
value4).FormatConditions(1).Interior.ColorIndex = 40

The issue is this...
If I open a workbook and apply the conditions manually it formats correctly.
However when the workbook that is generated through code is opened I find
that the formula has changed from...
Formula1:="=OR(AND($C8=1,F8199,F8<500),And($C81, F8299,F8<500))")

TO
"=OR(AND($C9=1,K9199,K9<500),And($C91,K9299,K9< 500))"

Note that the rows between the ranges are headers for the sections.
No cells are merged and the workbook that is used as a template has no
conditions on the worksheet.

This has been driving me nuts for a week now and I am at my wits end.
One friend stated that it might have to do with non adjacent cells being
selected and the index becomming corrupted.
Any and all help would be GREATLY appreciated.
Thanks in advance.
Environment:
Windows XP Service Pack 3
Excel 2007 all updates and patches applied
.NET 2.0 and 3.5 with service packs applied
Visual Web Developer 2008 Express Edition