Conditional formatting question

[GeorgeCunn]

If I enter a "Y" in B7, I want it to automatically enter a 0 in B9 using conditional formatting.

I tried using this formula (in conditional formatting) but it didn't work.

=if(B7="Y",0,"") I also tried if(B7="Y",0,0) Unfortunately neither worked.

As an option, if a "Y" was entered in B7, then it would automatically delete the contents in B9.

Thanking you in advance for your help.

 

[jasonb75]

You need to enter a formula in the cell, not conditional formatting.

 

[jasonb75]

To elaborate a bit more, you not change (or delete) what is in a cell with conditional formatting, you can only alter the appearance of the existing contents.

If B9 has a numeric value then you could hide it with conditional formatting, but the original value would still be there in the background, the actual value would not be changed. You would notice this if you have other formulas looking at B9.

 

[GeorgeCunn]

Thank you very much. That is what I thought but I also thought it never hurts to ask in case there is a work around.

 

[jasonb75]

The only workaround would be to delete the content of B9 using vba when B7 is changed to 'Yes'. The downside of this is that it would not 'undo' if you enter 'yes' in error.

I'm assuming that there are values in B9 which should be disregarded when 'Yes' is entered, is that correct? If so, then a conditional format formula of

=B7="yes"

Combined with a custom number format as below (this needs to be set in Conditional Formatting > Format > Number, not on the Home tab).

\0;\0;0;@

Will mask any non zero numbers as zero. Note that any formulas that refer to B9 will still see the original value, not the zero. If you have any such formulas then you will need to apply a condition to them as well.