formatting a cell
Posted by andrew miller on January 08, 2002 1:58 PM
I want my data to zero in only two cells on a sheet. Is there any way to format a cell so it shows a zero if it is blank without doing it to the whole page?
Posted by Tom Dickinson on January 08, 2002 4:27 PM
One method would be an If/Then in those 2 cells, putting in a text 0.
=If(value = 0, "0", value)
Posted by Florizel on January 08, 2002 4:59 PM
How would you do that?
Posted by Tom Dickinson on January 08, 2002 5:08 PM
Do what?
Florizel:
If by your question you mean how to keep the zero in 2 cells, while having it disappear in other cells where the value is zero, go to your options and un-check the "show zeros". If your question is on the formula itself, then just foolow the formula, substituting the word "value" with the operation desired. Just one note; a text zero will align left, so you will need to do an align right to make it look like a regular number.
Posted by Florizel on January 08, 2002 5:28 PM
Re: Do what?
But this assumes that the two cells in question contain a formula.
What about keyboard inputs of 0, or spacebar, or delete ?
Posted by Chris D on January 09, 2002 12:03 PM
Re: Do what?
Could conditional formatting do the job ?
Conditional format your whole sheet so that any cell value of "0" is formatted to white text - this will blank out ALL cases of "0"
then goto the cells you want to SHOW and delete the conditional formatting from them
would that work ?
Chris
Posted by Florizel on January 09, 2002 4:31 PM
Re: Do what?
Yes. Thanks very much. That took care of the zeroes.
In addition, to take care of blank cells and cells containing space(s), I've added the following macro :-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C2:C3")) Is Nothing Then
If Target.Value = "" Or _
Len(Application.WorksheetFunction _
.Substitute(Target.Value, " ", "")) = 0 _
Then Target.Value = 0
End If
Application.EnableEvents = True
End Sub
