Conditional Formatting in 2007 doesn't work after save to .xls

**sandeaj** · 02-20-2012, 05:11 PM

Hoping someone can help with this. I have created a simple conditional format whereby the text in one cell has white (invisible) text unless someone answers the question in another cell incorrectly, in which case the text turns red so the user can see it. This seems to work fine if I save the file as .xlsx and reopen in Excel 2007. However, if one of my teammates opens the file in Excel 2000 or 2003, the formatting doesn't work. I can look at the formatting rule and it appears like it should work, but it doesn't. I tried saving as Excel 97 - 2003 format, but then the formatting doesn't even work for me when I reopen in 2007. The weird thing is that if you open the file, see that the formatting isn't working, and then explicitely change the font in that cell to white (even though it already appears to be white), the formatting starts working.

I'm attaching a sample file and would appreciate any thoughts on how to get around this issue. (At least I think I attached the files?)

Thanks,

Mandy

**JieJenn** · 02-20-2012, 05:18 PM

I have dealt with xls format for awhile but I know fact that M$ had a major upgrade on the conditional formatting. The alternative I can think of is using VBA like the code below

Please Login or Register  to view this content.

**sandeaj** · 02-20-2012, 06:10 PM

Thank you for the quick response! VBA is a little beyond my Excel skills. Can anyone think of a simpler solution?

**JieJenn** · 02-20-2012, 06:55 PM

The only other alternative solution I can think of is copy and paste the rule from Excel 2007 to 2003. It is labor work, but the only way I can think of without going through VBA.

**celestesutton** · 02-24-2014, 01:04 PM

I am having this same problem with conditional formatting using icon sets in Excel 2007. I don't even have Excel 2003. I have crappy Vista and Microsoft Office 2007. Anyway, I set up this same conditional formatting in a few areas, it all worked fine with no problems at all. I tested it thoroughly and I saved the file as filename.xlsx (which is the standard file extension for excel 2007). I reopened it this morning to print it out and turn it in, and the conditional formatting was all messed up. As I mentioned, I was using the icon sets. The icons were 5 colored arrows pointing different directions. A green one pointing up, 3 yellow ones - 1 pointing 45 degrees up, 1 pointing sideways, 1 pointing 45 degrees down, and a red arrow pointing down. When I opened the file all the arrows are green pointing up, which happens to correspond to the nunber 5. I looked in the the individual cells and the original numbers ranging from 1 -5 are in there, they don't all save 5. I tried a manual calculation, and I tried entering in new data in blank cells that has the conditional formatting. Nothing worked. Anyone have any idea what is going on? I use conditional formatting all the time, and I have never had this problem. I've never used the icons, so it must have something to do with that? I should mention that I also tried saving this file as filename.xlsm (macro enabled excel spreadsheet) and that didn't work either. Am I missing something very obvious???? I'm pretty annoyed right now. I spent forever getting this to work right. Any help would be appreciated.

**arlu1201** · 02-28-2014, 01:24 AM

celestesutton,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**celestesutton** · 02-28-2014, 07:12 AM

Thanks, I am very impressed with this site, and I just exchanged some emails with the customer service/ help support team about how this all works. So I won't be repeating this mistake again! Thank you, for helping me.