Put date for specific cell according to the condition o another cell and clear cells according to condition VBA codes are not working together

[bahadir70]

I asked previously 2 codes from the experienced members and I got vba codes, thanks for the codes. they are working well separately. but I want them working together.

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"

also,

When I select the classification section "Positive Obs." in column "K" (there are 3 conditions here 1-Positive Obs., 2- Unsafe Act., 3-Unsafe Cond.) and condition "Closed" in column "R", I want the content of the related line column "M" and "Q" content clear (only contents, not formats and conditional formats).

I have seperate codes and can send upon request so the venerable member can maybe explain to me , just information, I am very new and old person (50 yrs).

 

[DanteAmor]

Try the following code in your sheet events.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("K:K, R:R"), Rows("3:" & Rows.Count)) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Value
      Case "Open"
        Range("Q" & Target.Row).ClearContents
      Case "Closed"
        Range("Q" & Target.Row).Value = Date
      Case "Positive Obs.", "Unsafe Act.", "Unsafe Cond."
        If Range("R" & Target.Row).Value = "Closed" Then
          Range("M" & Target.Row).ClearContents
          Range("Q" & Target.Row).ClearContents
        End If
    End Select
  End If
End Sub

 

[bahadir70]

Try the following code in your sheet events.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("K:K, R:R"), Rows("3:" & Rows.Count)) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Value
      Case "Open"
        Range("Q" & Target.Row).ClearContents
      Case "Closed"
        Range("Q" & Target.Row).Value = Date
      Case "Positive Obs.", "Unsafe Act.", "Unsafe Cond."
        If Range("R" & Target.Row).Value = "Closed" Then
          Range("M" & Target.Row).ClearContents
          Range("Q" & Target.Row).ClearContents
        End If
    End Select
  End If
End Sub

that code for the date section ok working, but second part is not.

I mean when I select positive observation at column "k" part and closed at column "R" I want to clear the contents in column"m" and "q" .for the related line.

Because my supervisors are putting date as a mistake to these columns which is wrong, because as you can see, positive observation cant have target date and correction date.

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("K:K, R:R"), Rows("3:" & Rows.Count)) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Value
      Case "Open"
        Range("Q" & Target.Row).ClearContents
      Case "Closed"
        Range("Q" & Target.Row).Value = Date
      Case "Positive Obs."
        If Range("R" & Target.Row).Value = "Closed" Then
          Range("M" & Target.Row).ClearContents
          Range("Q" & Target.Row).ClearContents
        End If
    End Select
  End If
End Sub

 

[bahadir70]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("K:K, R:R"), Rows("3:" & Rows.Count)) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Value
      Case "Open"
        Range("Q" & Target.Row).ClearContents
      Case "Closed"
        Range("Q" & Target.Row).Value = Date
      Case "Positive Obs."
        If Range("R" & Target.Row).Value = "Closed" Then
          Range("M" & Target.Row).ClearContents
          Range("Q" & Target.Row).ClearContents
        End If
    End Select
  End If
End Sub

when I select open, it is deleting the dates, that is ok, but I need another condition, if positive is selected and closed selected, it must delete the date at column m and q.

it is not doing.

is it possible to send you the file?

 

[DanteAmor]

     Case "[B][COLOR=rgb(0, 168, 133)]Closed[/COLOR][/B]"
        Range("Q" & Target.Row).Value = Date
      Case "[B][COLOR=rgb(0, 168, 133)]Positive Obs.[/COLOR][/B]"
        If Range("R" & Target.Row).Value = "[COLOR=rgb(0, 168, 133)][B]Closed[/B][/COLOR]" Then

You can check that part in the macro, if the words are spelled correctly (uppercase, lowercase, spaces between words, dot)

 

[bahadir70]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("K:K, R:R"), Rows("3:" & Rows.Count)) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Value
      Case "Open"
        Range("Q" & Target.Row).ClearContents
      Case "Closed"
        Range("Q" & Target.Row).Value = Date
      Case "Positive Obs."
        If Range("R" & Target.Row).Value = "Closed" Then
          Range("M" & Target.Row).ClearContents
          Range("Q" & Target.Row).ClearContents
        End If
    End Select
  End If
End Sub

unfortunatelly, not

is it possible to send you the file? by email?

 

[DanteAmor]

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[bahadir70]

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Thanks,

that is the link.

2-2020.07.XX_Daily Observation_Xxxx.xlsm

Shared with Dropbox

www.dropbox.com

some codes are there and working fine, just I need the last section which fixes my problem,

If I select "positive" at column "K" and continue filling the form (as a mistake my supervisors are writing date to column "M" and "Q" ) and when I come to column "R" and select "Closed" I want content of the column "M" and "Q" clear (not the format, just content).

thanks in advance, I really appreciate your efforts nad helps.

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("K:K, R:R"), Rows("3:" & Rows.Count)) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Value
      Case "Open"
        Range("Q" & Target.Row).ClearContents
      Case "Closed"
        Range("Q" & Target.Row).Value = Date
        If Range("K" & Target.Row).Value = "Positive Obs." Then
          Range("M" & Target.Row).ClearContents
        End If
      Case "Positive Obs."
        If Range("R" & Target.Row).Value = "Closed" Then
          Range("M" & Target.Row).ClearContents
          Range("Q" & Target.Row).ClearContents
        End If
    End Select
  End If
End Sub

In the file you shared you have other code.
Try my macro and don't modify it.
If you modify the macro and then you come here to say that my macro does not work, you must comment on what changes you made to the macro.
If you have problems with the macro, explain the sequence you do step by step, so that I can reproduce it and get to the problem and then I will correct it.