VBA Time Stamp Stack Error

[sassriverrat]

Thanks for helping

Teaching myself VBA and I keep getting a run time stack error on one of my sheets and it always highlights the same line. I can't seem to get a screenshot to post so I've copied and pasted the entire code below for the sheet. Most of the sheet's mathematics are purely formulas in the page- starting to convert stuff to VBA.

Thanks for the Help

Error Line: cells(4, 6) = Format(Now(), "dd-mmm-yyy")

VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(6, 23) <> "" Then
Cells(4, 6) = Cells(6, 23)
End If


If Cells(8, 18) <> "" And Cells(6, 23) = "" Then
Cells(4, 6) = Format(Now(), "dd-mmm-yy")


Application.EnableEvents = True
End If
End Sub

 

[MARK858]

If W6 is an override then you need to change the order...

Private Sub Worksheet_Change(ByVal Target As Range)
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    If Not Intersect(Target, Range("R8,W6")) Is Nothing Then


        If Cells(8, 18) <> "" And Cells(6, 23) = "" Then
            Cells(4, 6) = Date
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If

        If Cells(6, 23) <> "" Then
            Cells(4, 6) = Cells(6, 23).Value
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If

        If Cells(10, 26) = "'Yes" Then
            Cells(9, 18) = "'Exact"
        End If

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With

    End If
End Sub

As for the error...
a) I don't get an error when clearing W6
B) you haven't stated either the error number or the description it gives.

Btw you also haven't stated what F4 should show if both W6 and R8 are blank.

 

[sassriverrat]

It worked! You, Sir, are amazing! I added a piece to the code below for F4 to show "No Data Input" when the appropriate cells are blank.

Private Sub Worksheet_Change(ByVal Target As Range)    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    If Not Intersect(Target, Range("R8,W6")) Is Nothing Then




        If Cells(8, 18) <> "" And Cells(6, 23) = "" Then
            Cells(4, 6) = Date
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If


        If Cells(6, 23) <> "" Then
            Cells(4, 6) = Cells(6, 23).Value
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If
        
        If Cells(8, 18) = "" And Cells(6, 23) = "" Then
            Cells(4, 6) = "No Data Input"
        End If


        If Cells(10, 26) = "'Yes" Then
            Cells(9, 18) = "'Exact"
        End If


        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With


    End If
End Sub

I have learned a lot from this piece, so seriously, thank you.

A side question to continue learning (this is a report that I always looks the same on the printable area but I automate more as I learn!)-

I'd like to add a button that will reset all data fields and be ready to re-run all the formulas. The button itself is easy and I figure the sub ddd() is my newly learned trick to rerun the formulas. Is there a macro to be written that can 1. Reset a specified list of cells and 2. When selected, can open a dialog box to ask for a confirmation?

Thanks again!!!!