Runtime error '1004' no cells were found for Worksheet_Change

[brocazaria]

I've attached my code below. The code works as I intended, but after running it displays the error "Runtime error '1004' no cells were found".

Basically I want the code to change cell G3 to display today's date and return the value, if the formula in cell F3 changes. Anyone have any suggestions as to how to get rid of the error, or show me how to make the code run properly?


Private Sub Worksheet_Change(ByVal Target As Range)

' When cell G3 (a formula referencing other cells) is changed, run the code
' Changes cell G3 to post the current date and time and return the value
If Target.Dependents.Address = Range("F3").Address Then
'
' NOW Macro
' Makes the cell display today's date
'
Range("G3").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("G3").Select
Selection.NumberFormat = "d-mmm h:mm"
'
' PasteVal Macro
' Copies the current cell and pastes the value
'
Range("G3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End Sub

 

[G12]

Hi.

Save this in to the macro enabled workbook and I think it'll work for you. I think the OP had incorrect cell references, so I've adjusted here. Let me know if I'm missing something.

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target = Sh.Range("F3") Then

Range("G3").Value = Format(Now(), "d-mmm h:mm")

End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel
If the formula looks at cell on the same sheet, try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCell As Range

   Set KyCell = Range("F3")
   On Error Resume Next
   Set KyCell = Union(KyCell, KyCell.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCell) Is Nothing Then
      With Range("G3")
         .Value = Now
         .NumberFormat = "d-mmm h:mm"
      End With
   End If
End Sub

 

[brocazaria]

Hi.

Save this in to the macro enabled workbook and I think it'll work for you. I think the OP had incorrect cell references, so I've adjusted here. Let me know if I'm missing something.

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target = Sh.Range("F3") Then

Range("G3").Value = Format(Now(), "d-mmm h:mm")

End If
End Sub

Thank you for your reply. The code works well, the only thing is i still need it to return the value of the cell, or else every time i open the excel sheet it will update the date to the current date and time. Do you have any suggestions for this?