I apologise i posted a wrong code in my second post. i tried to put the end if before and after and both methods didn't work !!!
as could see the 2% in column 3 in the table didn't change to 0
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRenewedCDs As Range
Dim CD As Range
Application.EnableEvents = False
Set MyRenewedCDs = Range("J47:J71")
If Not Intersect(Target, MyRenewedCDs) Is Nothing Then
For Each CD In MyRenewedCDs
If CD.Value = "" Then CD.Offset(0, 2).Value = 0
End If
Next CD
Application.EnableEvents = True
End Sub