Conditional Formatting in Excel 2010

[sabbur]

I've use the following rule in Excel 2007 for several years: =RIGHT($B1,5)="Total" . It has worked great. I'm trying to use it on the same spreadsheet in Excel 2010 and it is not working. How can I make it work to highlight the rows that have "Total" in them.

 

[Joe4]

The first thing to check is to make sure that your range reference lines up on your new spreadsheet (i.e. edit one of the existing Conditional Formatting formulas and verify it is looking in the correct cell, and an appropriate formatting option has been selected).

 

[sabbur]

My range reference is good. I had another employee here pull it into Excel 2007 and run the rule. It ran great, did what it was supposed to. I opened it up and the right rows were highlighted, but they were highlighted to the left of any cells with data in them.

Deb.

 

[Joe4]

My range reference is good. I had another employee here pull it into Excel 2007 and run the rule. It ran great, did what it was supposed to. I opened it up and the right rows were highlighted, but they were highlighted to the left of any cells with data in them.

If they were highlighted to the left of any cells with data in them, that tells me that your range reference may be off one cell!

Pick one row where it doesn't work, and answer all of the questions below:
- What cell is the data in?
- What is the value of the data in that cell?
- What cell is the conditional formatting applied to?
- What is the conditional formatting formula in that cell?

 

[sabbur]

I am lost. How could the rows highlight correctly, the file is saved and closed, I open it in Excel 2010 and the rows are highlighted incorrectly? Wouldn't the range be the same since it is the same spreadsheet? I cleared any conditional formatting before the other employee ran the rule.

Debbie

 

[Joe4]

If it works correctly in Excel 2007, and then the file is re-opened in Excel 2010, and not a single thing is changed on the file (and I stress that point), and the conditional formatting no longer works properly, then I agree with you, that doesn't seem to make any sense, unless there is some sort of Event Procedure Macro in the file interfering with it.

I still recommend checking the items I suggested, so you can see if anything has changed. If it has, then that begs the question how and why?

 

[cummingsea]

The two problems I had when I used Excel 2007 and upgraded to Excel 2010 was as follows
1. in some but not all files some of the formulas would insert " in them example >=136 it would somehow change to ">=136" and would not work until I removed the " .
2. It would change the reference if I had C32 it would change it to something like C10085.

So I would have to fix them, I did not have this problems when I swapped to Excel Mac 2011

 

[Joe4]

That's really odd, sounds like it could be some sort of bug.

I would recommend making sure that you have run all the latest patches for Excel 2010 (in case they have already identified and corrected the bug).