Blank Cells to display as $0

[willow1985]

I am sure this is easy but I cannot remember how to do it and cannot find what I am looking for online.
I am making a calculator in excel and want certain ranges of blank cells to show as $0 before anything is entered, however if the user clears the cell it will revert back to displaying $0.

I would also like to be able to change the $0 value to a default value of my choice for one or more of the cells. Can this be achieved in VBA running in the background?

Help with this would be greatly appreciated.

Thank you

 

[ISY]

Hi

 

[Eric W]

It sounds like if someone presses "delete", then you want a given value to populate the cell? If so, populate the range with the default value. Right click on the sheet tab on the bottom and select View Code. Paste this code in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("B2:B8")) Is Nothing Then
        If Target.Value = "" Then Target.Value = 0
    End If
    
    If Not Intersect(Target, Range("C2:C8")) Is Nothing Then
        If Target.Value = "" Then Target.Value = 100
    End If
    
    Application.EnableEvents = True

End Sub

Change the ranges to match your sheet (B2:B8 and C2:C8 in this example). Change the values to what you want (0 and 100 here). Let us know if this is what you want.

 

[EXCEL MAX]

Hello Willow, you can try to do that with custom cell formatting.
[<>0]#,##0.00;;"$0"