toggle formatting

[bigfoot]

hi
right now i have a sheet with conditional formatting which consists of, 1-if percentage is greater than 0% make result green, and 2-if percentage is less than 0%, make result red. this works fine. what i'm trying to do is, lets say i place an 'x' in cell A1, i want all the cells on the sheet that have a positive percentage to become blank. when the 'x' is removed, the green percentage should return. any suggestions on this. thanks for any help.

 

[JamesW]

Hi,

How are your percentages populated? If it's a formula you can maybe do something like:

=if(a1="x","",b4/c4)

If they are not formulas then you are looking at a VB route, with maybe some helper cells (I'm not sure how you will store the current data and 'restore' it once the x has gone).

 

[bigfoot]

hi. thanks for the response. i tried the formula, and for some reason it did not work. i only wish to blank out positive numbers, and there is a condition that changes the color of the font, if its negative or positive.the best i got, is the color disappeared, but the cell did not go blank. is there anything else i can try?

 

[bigfoot]

any thoughts, anyone

 

[Sektor]

https://my.syncplicity.com/share/ou0bvgnm2r/bigfoot.xlsx

 

[bigfoot]

i cant open the file, unless i download additional files to make our versions of excel compatible. however, at work,i cannot download files like that. i am using excel 2003. thanks.

 

[Sektor]

Here's solution for Excel 2003.
Go to Conditional Formatting and set following (for cell F6):

1. Condition 1. Formula: =AND(A1="x";$F$6>0). In Format set white font color (this will "hide" value).
2. Condition 2. Formula: =$F$6<=0. In Format set red fill color.
3. Condition 3. Formula: =$F$6>0. In Format set green fill color.

 

[bigfoot]

thank you very much. works like a charm.