Runtime Error: 1004 - Execute Macro when a condition is met in two columns

[Tombies]

First off, sorry to ask about runtime error 1004, but I am not that familiar with VBA and I cannot find a solution anywhere that I can make sense of.


The problem I get is: Run-Time error '1004' : Application-defined or object-defined error

Here is my code:

Sub DateInsertion()
'
' DateInsertion Macro
'
' Keyboard Shortcut: Ctrl+m


Dim QuoteSubmission As String, DateSubmitted As Range


    With Sheets("T3169")
        QuoteSubmission = .Range("H2:H" & Rows.Count).End(x1Up).Row
        For Each DateSubmitted In .Range("I2:I" & QuoteSubmission)
            If (DateSubmitted.Value = "") And (DateSubmitted.Offset(0, -1) = "Yes") Then
            DateSubmitted.Value = Date
        
            End If
        Next DateSubmitted
    End With
 


End Sub

The excel sheet itself consists of column "H" with Yes or No. Column "I" is a short date. When a cell in Row Nth, col. 'H' = "Yes", I want the adjacent cell in col. 'I' to enter the date.

The excel sheet is a dynamic sheet and it updates from a database. Upon refresh, if a cell in column 'H' changes to "Yes", the VBA code does not detect the change, hence it will not enter the date. That is why I am using a macro with a shortcut to do it rather then programming a "Sub Worksheet_Change".

Any help is appreciated!

 

[Michael M]

Maybe this, which will update when the worksheet recalcs

Sub Workbook_worksheetCalculate()
'
' DateInsertion Macro
'
' Keyboard Shortcut: Ctrl+m


Dim QuoteSubmission As String, DateSubmitted As Range, lr As Long
lr = Sheets("T3169").Cells(Rows.Count, "H").End(xlUp).Row
    With Sheets("T3169")
      For r = 2 To lr
            If Range("I" & r).Value = "" And Range("H" & r).Value = "Yes" Then
            Range("I" & r).Value = Date
            End If
        Next r
    End With
End Sub

 

[Tombies]

Maybe this, which will update when the worksheet recalcs

Sub Workbook_worksheetCalculate()
'
' DateInsertion Macro
'
' Keyboard Shortcut: Ctrl+m


Dim QuoteSubmission As String, DateSubmitted As Range, lr As Long
lr = Sheets("T3169").Cells(Rows.Count, "H").End(xlUp).Row
    With Sheets("T3169")
      For r = 2 To lr
            If Range("I" & r).Value = "" And Range("H" & r).Value = "Yes" Then
            Range("I" & r).Value = Date
            End If
        Next r
    End With
End Sub

It worked! Thank you!