Conditional Formatting Problem from Excel 2002 to Excel 2007

**Rob1974** · 04-23-2010, 11:11 AM

Hi, Could someone help me fix this please.

I have created a workbook in Excel 2002 and I have used conditional formatting to hide text in cells which <= 0. I have chosen to turn the text white to make it invisible.

I have sent this workbook to someone who is using Excel 2007, but there is a problem with this conditional formatting.

In 2002, when you click into a cell and type a value it appears in black as you type, and the conditional formatting is applied 'after' you enter the value. However in 2007, it appears the conditional formatting is applied to the text as you type it in - So when you click into a blank cell, you can't see the text as you type, because it is white. Once the value is entered and provided it is not <=0, it shows it correctly.

**DonkeyOte** · 04-23-2010, 11:32 AM

the text should be visible in the formula bar though correct ?

**Rob1974** · 04-23-2010, 11:35 AM

Yes, you can still see the text in the formula bar, just not in the cell as you type it in.

**DonkeyOte** · 04-23-2010, 11:44 AM

AFAIK there's no means of altering this default behaviour (and given you can see the text you're typing in the formula bar I guess that's the only workaround)

(because the formatting is set via Conditional Formatting there's little you can do it seems - I had wondered about using the doubleclick event but that won't work either)

**Rob1974** · 04-23-2010, 11:49 AM

Ok, thanks for that. Would there be another way of approaching this in Excel 2007, of hiding cells with a numeric value of <=0, other than conditional formatting?

**DonkeyOte** · 04-23-2010, 11:52 AM

You could use a Custom Format if that's what you mean, ie a custom format applied to the cell of: #;;;@

would mask anything <=0 as blank and the text would be visible on edit (change format for +ve numbers as per preference - ie #,###0.0 rather than just # etc...)

**Rob1974** · 04-23-2010, 11:55 AM

Ok, I'll have a play with that later. Many thanks for your fast replies.

**Rob1974** · 04-23-2010, 08:12 PM

Solved - I managed to fix the problem in Excel 2007, by firstly using Excel Options > Advanced > UNCHECK Show a zero in cells that have a zero value.

Secondly I amended the conditional formatting to only look for values <0, rather than <=0 to deal with negative numbers.

Now working as intended.

**DonkeyOte** · 04-24-2010, 03:06 AM

I confess I'm slightly confused by the above "resolution" in so far as the Conditional Formatting rule will still act per your initial post - ie negatives would be invisible within the cell whilst in edit mode and this (I thought) was your problem, no ?
(or are you saying you don't have -ve's ?)

IMO (FWIW) where viable (as is the case here) a Custom Format solution is always preferable to Conditional Formatting given the latter's super volatility.

Note also that "show a zero" is a Workbook/Worksheet level setting so will apply to all cells within the Workbook/Worksheet so you can't differentiate by specific ranges should you ever want to.

On that basis I'd still suggesting applying a Custom Format to the range of interest of: #;;;@

Custom Formats in traditional format work along the lines of:

Format For > 0; Format for < 0; Format for 0; Format for Text

For more info. see any/all of:

http://pubs.logicalexpressions.com/p...cle.asp?ID=414
http://www.ozgrid.com/Excel/CustomFormats.htm
http://simoncpage.co.uk/blog/2008/09...er-formatting/

Disregard the above of course as is your prerogative - it is just my two pennies worth