Running Macro after data has been entered into a cell

[brhyne]

Hello!

I have been trying to get the below code to run after I have entered a date in column B and exit the Cell. The code works perfectly if I go out of the cell in column B and then go back into it. After reentering B the code runs flawlessly. I have searched relentlessly for a solution with no resolve. if anybody has any Ideas I would be forever grateful.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("b2:b1048576")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

Select Case ActiveCell(1, 1).Value

Case Is = ""
ActiveCell(1, 22).Value = ""
ActiveCell(1, 0).Value = ""


Case Is < Date
ActiveCell(1, 22).Value = Date
ActiveCell(1, 0).Value = Date

Case Is >= Date
ActiveCell(1, 22).Value = ActiveCell(1, 1).Value
ActiveCell(1, 0).Value = Date

End Select

End If

End Sub


I'm new to this forum so I hope I'm posting to the right area.

Thanks in advance for your help!

 

[Datsmart]

Try using:
"Private Sub Worksheet_Change(ByVal Target As Excel.Range)"
instead of:
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)"

 

[brhyne]

Thanks John! I tried your suggestion, unfortunately it did not work.

 

[Datsmart]

Sorry,
If you use Worksheet_Change you need to use "Target" in your code, as "Activecell" is normally one cell lower after hitting Enter.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("b2:b1048576")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
            Case Is = ""
            Target(, 22).Value = ""
            Target(, 0).Value = ""
            
            Case Is < Date
            Target(, 22).Value = Date
            Target(, 0).Value = Date
            
            Case Is >= Date
            Target(, 22).Value = Target(, 2).Value
            Target(, 0).Value = Date
        End Select
    End If
End Sub

 

[brhyne]

Thanks John!, I was able to resolve the issue using the following code.

Option Explicit

Public Sub Worksheet_Change(ByVal Target As Excel.Range)
   Sheets(1).Protect Password:="Start123", _
          UserInterFaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
          AllowSorting:=True, AllowFiltering:=True


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



Select Case Target(1, 1).Value

Case Is = ""
Target(1, 22).Value = ""
Target(1, 0).Value = ""

Case Is < Date
Target(1, 22).Value = Date
Target(1, 0).Value = Date

Case Is >= Date
Target(1, 22).Value = Target(1, 1).Value
Target(1, 0).Value = Date

End Select
Application.EnableEvents = True

End If
   
End Sub