highlight row / column with conditional formatting

[markstro]

I have a workbook saved as Excel 97/2003 workbook*.xls (compatibility mode) that uses the following VBA and conditional formatting to highlight the row/column the selected cell occupies.

VBA
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.ScreenUpdating = True

Conditional Formatting formula
=OR(AND(CELL("row")=ROW(),COLUMN()<=CELL("col")),AND(CELL("col")=COLUMN(),ROW()<=CELL("row")))

This combination works just fine in the 97/2003 workbook, but not another saved as *.xlsm format.

What changed between Excel 97-2003 and Excel 2007??
I run a compatibility check and get an overlapping conditional formatting problem, not sure how to fix it or if it is what is effecting my formatting to work.

I have used this formatting / code successfully on other workbooks saved as 97/2003 but have problems getting it to work in Excel 2007.

 

[markstro]

I have one more rule that makes any value of zero text the same as the fill color, I only see the values above zero that way, all zeros are hidden.

 

[Andrew Poulsom]

That's the first time you have mentioned another rule. Do you also have that rule in your Excel 2003 workbook?

There are 2 other ways of suppressing zeroes. Firstly, you can uncheck 'Zero values' under Window options on the View tab of the Tools|Options dialog. Secondly, you can format the cell as eg:

0;0;

the key being the second semicolon.

One of the advantages of those 2 methods over yours is that you still don't see the zeroes in cells that are highlighted when a range is selected. The other is that they are built-in, so there will be no calculation overhead.

 

[markstro]

thank you for that tip, I will definitely use it. Never thought to ask if there was another way to do that.

I will delete that rule and see what happens. Back to you soon.

 

[markstro]

OK, I've deleted that rule, now I only have one rule, still no joy with the VBA / formatting?

 

[Andrew Poulsom]

Sorry, I can't reproduce your problem. As I said it works for me in Excel 2007.

 

[markstro]

Not sure if this made it work, but after I re-pasted the conditional formula from the working worksheet, it worked, same vernacular, just started working after I did that. Thanks for all your help Andrew. If anything, I learned the hidden zero tip.