VBA Clear Contents Various Methods Not Working

[3dmikey]

Hi everyone,

I need help with a VBA to clear contents of a cell based on the value of another cell. I am tracking a to do list and I have options for either "Open", "In Progress" or "Closed" in Row H. When I mark an item as "Closed" a current VBA code will insert the date in Column J (2 row offset). However, if something is reopened I will mark it as "Open" and I want to clear the previous date closed. I have tried many .clearcontents codes based on the value of the cell in column H being "Open" but with no success. I have attached my current "Closed" VBA code to see if there are any conflicts?

Any help would be appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)


  Dim Rng As Range
  Dim StartRow As Long


  
    
      StartRow = 9
    
    
      If Target.Row < StartRow Then Exit Sub
    
    
      Set Rng = Intersect(Target, Range("H:H"))
    
    
      If Rng Is Nothing Then Exit Sub
    


   
     Application.EnableEvents = False


   
      If Rng.Value = "Closed" Then
         Target.Offset(0, 2).Value = Format(Now(), "dd-mmm-yyyy")
      End If


   
     Application.EnableEvents = True


End Sub

 

[AlphaFrog]

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    [color=darkblue]If[/color] Target.Row < 9 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]If[/color] Target.Column <> 8 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=green]'Column H[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] Reenable
    Application.EnableEvents = [color=darkblue]False[/color]
    
    [color=darkblue]If[/color] Target.Value = "Closed" [color=darkblue]Then[/color]
        Target.Offset(0, 2).Value = Format(Now(), "dd-mmm-yyyy")
    [color=darkblue]ElseIf[/color] Target.Value = "Open" [color=darkblue]Then[/color]
        Target.Offset(0, 2).ClearContents
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
Reenable:
    Application.EnableEvents = [color=darkblue]True[/color]
    [color=darkblue]If[/color] Err.Number <> 0 [color=darkblue]Then[/color] MsgBox Err.Description, vbCritical, "Error " & Err.Number
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[3dmikey]

Sorry for the delay but this was perfect! Thanks for your help.

 

[AlphaFrog]

You're welcome. Thanks for the feedback.