Macro that automatically inputs "RECHECK STATUS" in cell when date changes in another cell

Hello,

I am having trouble figuring out code to have excel automatically input "RECHECK STATUS" in one cell when the date changes in another cell.

The dates will be in column D, and "RECHECK STATUS" will be in column E.

So, for example, if a user changes the date in cell D6, how can I make excel automatically input "RECHECK STATUS" in E6?

And, for example, if a user changes the date in cell D15, how can I make excel automatically input "RECHECK STATUS" in E15? (Notice I am trying to make this work for the full range of columns D and E).

Also, I would like the ability to delete "RECHECK STATUS" after status is checked.

Thank you to anyone who can come up with a solution.

Thanks very much! Works great. How can we make "RECHECK STATUS" red and bold?

Try This.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
        If Not Intersect(Target, Range("D:D")) Is Nothing Then

            Target(1, 2).Value = "Check Status"
            Target(1, 3).ClearContents

            Target(1, 2).select

    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = 255
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

   End If
        
            If Not Intersect(Target, Range("F:F")) Is Nothing And Target.Cells.Value = "Ok" Then
            
            Target(1, 0).ClearContents
            Target(1, 1).ClearContents

        End If
Application.EnableEvents = True
End Sub

This is a sheet specific macro.

Enter anything in column D and Column E will ask to Check Status.

Enter Ok into Column F will Clear entries in Column E and F.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Target, Range("D:D")) Is Nothing Then

            Target(1, 2).Value = "Check Status"
            Target(1, 3).ClearContents
        End If
        
            If Not Intersect(Target, Range("F:F")) Is Nothing And Target.Cells.Value = "Ok" Then
            
            Target(1, 0).ClearContents
            Target(1, 1).ClearContents

        End If

End Sub

Thank you very much. You even threw in another great technique for validating check status. How can we make "Check Status" red and bold?