Sign in to follow this  
Followers 0
newsman220

Excel Conditional Formatting gets bad references

3 posts in this topic

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?

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0