Worksheet change offset BUT ignore if offset value exists

[john316swan]

I have a worksheet change macro that both updates an offset cell value with a date & a selection of cells with corresponding offset dates, however I am trying to update it so that it only inputs date if the offset cell is blank. Here is the code that works:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 23 And Target.Cells.Count > 1 Then
    Application.EnableEvents = False
        For Each cell In Selection
            If cell.Value = "done" Then
                Target.Offset(0, 1) = Date 'However it just overwrites any previous date that was entered
            End If
        Next cell
    Application.EnableEvents = True
    Exit Sub
End If

'This portion of the code works perfectly, I just can't get the selection to work properly    
If Target.Column = 23 Then
    If Target.Value = "done" And Target.Offset(x, 1) < 1 Then
        Application.EnableEvents = False
        Target.Offset(x, 1) = Date
        Application.EnableEvents = True
    End If
End If
    
End Sub

Here is the code that doesn't work (run time error '13' type mismatch):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 23 And Target.Cells.Count > 1 Then
    Application.EnableEvents = False
        For Each cell In Selection
            If cell.Value = "done" Then
                If Target.Offset(0, 1) < 1 Then 'This is line of error
                    Target.Offset(0, 1) = Date
                End If
            End If
        Next cell
    Application.EnableEvents = True
    Exit Sub
End If

'This portion of the code works perfectly, I just can't get the selection to work properly 
If Target.Column = 23 Then
    If Target.Value = "done" And Target.Offset(x, 1) < 1 Then
        Application.EnableEvents = False
        Target.Offset(x, 1) = Date
        Application.EnableEvents = True
    End If
End If
    
End Sub

Thanks in advance for your help, hopefully this is low hanging fruit.

 

[Joe4]

Since you are looping through each cell in the Target, you should be using cell, not Target.
I would recommend changing this whole block:

        For Each cell In Selection
            If cell.Value = "done" Then
                If Target.Offset(0, 1) < 1 Then 'This is line of error
                    Target.Offset(0, 1) = Date
                End If
            End If
        Next cell

to this:

        For Each cell In [COLOR=#ff0000]Target[/COLOR]
            If cell.Value = "done" Then
                If [COLOR=#ff0000]cell.[/COLOR]Offset(0, 1) < 1 Then 
                    [COLOR=#ff0000]cell[/COLOR].Offset(0, 1) = Date
                End If
            End If
        Next cell

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 23 Then
        Application.EnableEvents = False
            For Each [COLOR=#0000ff]cell[/COLOR] In Target
                If [COLOR=#0000ff]cell[/COLOR].Value = "done" Then
                    If [COLOR=#0000ff]cell[/COLOR].Offset(0, 1).Value = "" Then
                        [COLOR=#0000ff]cell[/COLOR].Offset(0, 1) = Date
                    End If
                End If
            Next cell
        Application.EnableEvents = True
    End If
End Sub

 

[DanteAmor]

Beaten!

 

[john316swan]

Thank you both!

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.