VBA Sleep code time interval question

Create a new standard module or use an existing one (like Module1) and add the API code at the top of the module

Option Explicit

#If VBA7 Then

    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64-Bit versions of Excel

#Else

    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32-Bit versions of Excel

#End If

Then in your worksheet module update your existing code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("J34")) Is Nothing Then Exit Sub
    
    MsgBox "REMINDER: After filling out info for this row, click the Archive and Reset Sheet button", vbOKOnly
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbRed
    Sleep 500 'milliseconds (pause for 0.5 second)
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbBlue
    Sleep 500 'milliseconds (pause for 0.5 second)
    
End Sub

Thanks Jeff, I tested this out but it actually isn't changing the shapes color after I click "OK" on the message box that pops up when typing something into J34. It was changing the color before with the Application.Wait line but for this after clicking OK, it stays the same color and has a bit of a delay before you can click in another cell. That delay in being able to click was expected, but the lack of color change wasn't. Were you getting a different result?