Excel Conditional Formatting gets bad references

[newsman220]

Good evening. I'm working with a script to pull in data from text files and a web query page, put them in Excel, and apply some conditional formatting to highlight problems in the data.

Everything is working except trying to create a conditional rule to compare 2 columns. When they differ, one should get a red background.

I'm using a similar rule to highlight another column based on whether the cell contains "Good" or "Bad" and that works.

What appears to be happening is when I use COM to create a reference such as =B2, the worksheet shows the rule as having =D3.

I'm not sure where that's coming from.

Here's a chunk of the code I'm using.

If $SAPDonglePosition Then

With $workbook.ActiveSheet.Range("$" & $SAPDonglePosition & "$2:" & "$" & $SAPDonglePosition & "$200").FormatConditions.Add(2, 5, "=C2 <> B2")

.Font.Colorindex = 30

.Interior.ColorIndex = 22

EndWith

EndIf

$SAPDonglePosition is either the letter of the column that data's in, or the value False.

I've tried this a number of ways, including having the rule cell-based instead of formula-based.

In this instance, the "=C2<>B2" comes out as "=E3<>D3" which puts it two columns to the right and one column down. I'm seriously at a loss as to how that happens.

Can anyone spot where I'm going wrong?

[Juvigy]

Put a $ and change "=C2<>B2" to "=$C2<>$B2"

[newsman220]

With that nudge, I got it working. I changed it back to cell-based and used inequality as the basis for the test.

Oddly, the rule still changed the second digit.

If $SAPDonglePosition Then

With $workbook.ActiveSheet.Range("$" & $SAPDonglePosition & "$2:" & "$" & $SAPDonglePosition & "$200").FormatConditions.Add(1, 4, "=$B1"); & Chr(Asc($SAPDonglePosition)-1) & "2")

.Font.Colorindex = 30

.Interior.ColorIndex = 22

EndWith

EndIf

So instead of understanding it, I took the black box approach and changed the digit to 1 instead of what I thought it should be, 2. With that, everything worked.

I suspect because I'm applying my rule to start at the second row instead of the first, Excel compensated for me and added one to the relative row reference.

If I get time, I may test on that. I still need to change the code back to let it figure out the correct position, instead of hardcoding it.

Thanks for your help, Juvigy.