Enter date or clear cell according to other cell's data

[bahadir70]

I have an excel sheet for my safety officers fill for daily reports. They are making mistakes while filling, so I want to make it more easy for them and for me as well.

As seen attached pictures, if R3 (condition) is "Open" if they put a date in "Q3" I want "Q3" will be cleared (only value-not format or conditional format)

and if they select "closed" at "R3" I want "Q3" will get the date on the form filled date. (but not like today() option, because today() option is updating the date when we open the form.

I did a data validation for R3 to select "Open" or "Closed"

Can anybody help me?

I am very new (just nothing )for VBA codes,

 

[DanteAmor]

Put the following code in the events of your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  
  Set rng = Intersect(Target, Range("R2:R" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      Application.EnableEvents = False
      If c.Value = "Open" Then
        Range("Q" & c.Row).ClearContents
      ElseIf c.Value = "Close" Then
        Range("Q" & c.Row).Value = Date
      End If
      Application.EnableEvents = True
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[bahadir70]

Put the following code in the events of your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
 
  Set rng = Intersect(Target, Range("R2:R" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      Application.EnableEvents = False
      If c.Value = "Open" Then
        Range("Q" & c.Row).ClearContents
      ElseIf c.Value = "Close" Then
        Range("Q" & c.Row).Value = Date
      End If
      Application.EnableEvents = True
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

thank you very much,

when I select open, it is clering the cell for correction date.
but second section I could not it run.

example, when I select the "closed" instead of "open" I was asking is there any way to put the date when it has been closed ( I mean selection of the "closed" date).

 

[bahadir70]

thank you very much,

when I select open, it is clering the cell for correction date.
but second section I could not it run.

example, when I select the "closed" instead of "open" I was asking is there any way to put the date when it has been closed ( I mean selection of the "closed" date).

I have an excel sheet for my safety officers fill for daily reports. They are making mistakes while filling, so I want to make it more easy for them and for me as well.

As seen attached pictures, if R3 (condition) is "Open" if they put a date in "Q3" I want "Q3" will be cleared (only value-not format or conditional format)
that section is working.

and if they select "closed" at "R3" I want "Q3" will get the date on the form filled date. (but not like today() option, because today() option is updating the date when we open the form.
That section, I could not succed to work with this code.

 

[bahadir70]

Put the following code in the events of your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
 
  Set rng = Intersect(Target, Range("R2:R" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      Application.EnableEvents = False
      If c.Value = "Open" Then
        Range("Q" & c.Row).ClearContents
      ElseIf c.Value = "Close" Then
        Range("Q" & c.Row).Value = Date
      End If
      Application.EnableEvents = True
    Next
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

I have an excel sheet for my safety officers fill for daily reports. They are making mistakes while filling, so I want to make it more easy for them and for me as well.

As seen attached pictures, if R3 (condition) is "Open" if they put a date in "Q3" I want "Q3" will be cleared (only value-not format or conditional format)
that section is working.

and if they select "closed" at "R3" I want "Q3" will get the date on the form filled date. (but not like today() option, because today() option is updating the date when we open the form.
That section, I could not succed to work with this code.

 

[DanteAmor]

example, when I select the "closed" instead of "open"

My fault, I put "Close" instead of "Closed"

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  
  Set rng = Intersect(Target, Range("R2:R" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      Application.EnableEvents = False
      If lcase(c.Value) = lcase("Open") Then
        Range("Q" & c.Row).ClearContents
      ElseIf lcase(c.Value) = lcase("Closed") Then
        Range("Q" & c.Row).Value = Date
      End If
      Application.EnableEvents = True
    Next
  End If
End Sub

 

[bahadir70]

My fault, I put "Close" instead of "Closed"

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
 
  Set rng = Intersect(Target, Range("R2:R" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      Application.EnableEvents = False
      If lcase(c.Value) = lcase("Open") Then
        Range("Q" & c.Row).ClearContents
      ElseIf lcase(c.Value) = lcase("Closed") Then
        Range("Q" & c.Row).Value = Date
      End If
      Application.EnableEvents = True
    Next
  End If
End Sub

Perfect...

Now I have to study and tryto learn the logic.

Thanks a lot...

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[bahadir70]

I'm glad to help you. Thanks for the feedback.

and 1 more question, I think I am disturbing you but
at previously attached pictures, if I select "positive obs." in K column, is it possible to protect to enter the date for M and Q column?

For putting the date for all columns, I am using date picker VBA code (for your kind information" unfortunately I could not send the excel sheet here to explain clearly.

 

[bahadir70]

I add another option to above question,

in the previously attached pictures, if I select "positive obs." in K column, is it possible to protect to enter the date for M and Q column?

Or

Can it be possible, if "K" columns is "positive obs." and I select the "closed", clear "M" and "Q" columns dates (if as a mistake there was a date put), because normally positive observation mustn't have target date and closed date.

So with the adding that part, reporting sheet will not allow anybody make any mistake while filling.