Macro question - replacing formula

[Kutrenia]

I have a spreadsheet where dates are entered in a6:a49 either manually or automatically if cells f6:f49 contains data (names of cities). I'm trying to do a macro that would put the formula =IF(ISBLANK(f6:f49),"",NOW()) in cells a6:a49 if that cell's value is erased/deleted.

 

[thunder_anger]

you can try this

Sub ReplaceFormula()
Dim MyRng As Range
Dim MyCll As Range
Set MyRng = ActiveSheet.Range("A1:A10")
   For Each MyCll In MyRng
      If MyCll = "" Then
        MyCll.Offset(0, 1) = 0
        Else
        MyCll.Offset(0, 1) = Now()
      End If
   Next MyCll
End Sub

 

[gsistek]

Try using:

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim ARange As Range
Dim ACell As Range
Set ARange = Range("A1:A20")
For Each ACell In ARange
    If ACell.Offset(0, 5) = "" Then
        ACell = Now()
    End If
Next ACell
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Kutrenia]

Thank you both ... with a little tweaking it works great!