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).

 

[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
        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.

thx I did, it is deleting the date in "target date" section but not deleting the "closed out" date section.

 

[DanteAmor]

It doesn't help if you just say it doesn't work.
You must expose here the sequence of steps you perform on your sheet and what result you want.

 

[bahadir70]

It doesn't help if you just say it doesn't work.
You must expose here the sequence of steps you perform on your sheet and what result you want.

Thanks a lot again and again,



I think there is 4 steps.

• At columns D, if they put date in wrong format by using different separators or other mistakes, wrong dates, and continue filling the form. When they select the “contractor” at columns “E”, automatically put the today date to column “D”.
• While Continuing to fill the form. When column “K” “positive obs.” is selected, and if as a mistake there is date at column “M” and column ”Q”, after the selection of “Closed” at column “R” at “Condition” column, I want column “M” and “Q” contents cleared.
• And similar, if there is date at column “Q” “correction date” and if the condition at column “R” is selected open, I need correction date is cleared at column “Q”.
• And 1 more ?, if the column “K” is not “positive obs.” and condition column “R” is "closed", again put date to column “Q” today's date.

looks very complicated but I believe if we would sit together and I could explain face to face it would be very easy for you with your that much knowledge.

AGAIN AGAIN AGAIN Thanks a lot. even until now you spend a lot of time to help me.

 

[DanteAmor]

At columns D, if they put date in wrong format by using different separators or other mistakes, wrong dates, and continue filling the form. When they select the “contractor” at columns “E”, automatically put the today date to column “D”.

This point is new, it is not in your initial requirement.
___________________________________________________________________________

While Continuing to fill the form. When column “K” “positive obs.” is selected, and if as a mistake there is date at column “M” and column ”Q”, after the selection of “Closed” at column “R” at “Condition” column, I want column “M” and “Q” contents cleared.

What is the issue with this?
___________________________________________________________________________

And similar, if there is date at column “Q” “correction date” and if the condition at column “R” is selected open, I need correction date is cleared at column “Q”.

What is the issue with this?
___________________________________________________________________________

And 1 more ?, if the column “K” is not “positive obs.” and condition column “R” is "closed", again put date to column “Q” today's date.

This is also new, it does not exist in your initial requirement.

It is impossible for me to guess what to do if you did not mention them before.

So the macro has several errors or only one.
If it is one, we could focus on that failure and describe step by step what you are capturing and what result you want.

 

[bahadir70]

bahadir70 said:
At columns D, if they put date in wrong format by using different separators or other mistakes, wrong dates, and continue filling the form. When they select the “contractor” at columns “E”, automatically put the today date to column “D”.

This point is new, it is not in your initial requirement.

that code is doing that part as I requested from one member.

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 Target.CountLarge > 1 Then Exit Sub
If (Target.Row >= 3) And (Target.Column >= 5) And (Target <> "") Then
Cells(Target.Row, "D") = Date
End If
End Sub

 

[bahadir70]

While Continuing to fill the form. When column “K” “positive obs.” is selected, and if as a mistake there is date at column “M” and column ”Q”, after the selection of “Closed” at column “R” at “Condition” column, I want column “M” and “Q” contents cleared.

What is the issue with this?

with this, when I put closed, it is adding todays date to column "Q" which I want it is cleared.

 

[bahadir70]

And similar, if there is date at column “Q” “correction date” and if the condition at column “R” is selected open, I need correction date is cleared at column “Q”.

What is the issue with this?

that is fine, working.

 

[bahadir70]

And 1 more ?, if the column “K” is not “positive obs.” and condition column “R” is "closed", again put date to column “Q” today's date.

This is also new, it does not exist in your initial requirement.

and yes, that is new.

 

[DanteAmor]

What is the issue with this?

with this, when I put closed, it is adding todays date to column "Q" which I want it is cleared.

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("Q" & Target.Row).ClearContents
          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

I would like to solve one by one.

 

[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
        If Range("K" & Target.Row).Value = "Positive Obs." Then
          Range("Q" & Target.Row).ClearContents
          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

I would like to solve one by one.

that is perfect for positiv obs and open items working perfectly