Need a Conditional Formula

Hello,

I need a formula that as soon as I delete a cell the value to be automatically changed to '0'.
Please suggest

Thanks,
Shan

Which cell will be deleted? You want the same cell which is deleted to be changed to 0?

Yes, thats the trick

Right click on your sheet name and select "View Code". Then copy this code in the code window that appears -

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "" Then
    Target.Value = "0"
End If
End Sub

Then test it.

thats great .. cant we do that with a formula
anyway this solves .. many thanks

I am not sure if you can do it with a formula.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Hi arlette,

How can we do this for a specific range

Thanks,
Shan

Do you mean you want to select a range of cells and delete the contents?

Yes pls . say a specific column or a range of cells

This what you want ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
    For Each cell In Target
        If cell.Value = "" Then
            cell.Value = "0"
        End If
    Next
End Sub

Thanks Mike. I didnt realise that could be done.

Your welcome, probably better to Intersect the range.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
    On Error Resume Next
    For Each cell In Intersect(Range("a1:a20"), Target)
        If cell.Value = "" Then
            cell.Value = "0"
        End If
    Next
End Sub