VBA code for clearing content of cells depend on other 2 cells.

[bahadir70]

I have a question about the VBA code, I am very new and really dont know about VBA codes, my question is:

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

Is there any way and code possible for that?
Thanks in advance.

 

[offthelip]

try this in the worksheet change event code. ( right click the worksheet tab and select view code, paste this into the window.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K:K, R:R")) Is Nothing Then Exit Sub
rowno = Target.Row
If Cells(rowno, 11) = "Positive Obs" And Cells(rowno, 18) = "Closed" Then
 Application.EnableEvents = False
 Cells(rowno, 13) = ""
 Cells(rowno, 17) = ""
 Application.EnableEvents = True
End If
End Sub

 

[bahadir70]

try this in the worksheet change event code. ( right click the worksheet tab and select view code, paste this into the window.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K:K, R:R")) Is Nothing Then Exit Sub
rowno = Target.Row
If Cells(rowno, 11) = "Positive Obs" And Cells(rowno, 18) = "Closed" Then
Application.EnableEvents = False
Cells(rowno, 13) = ""
Cells(rowno, 17) = ""
Application.EnableEvents = True
End If
End Sub

I think it clashed with other code, actually I have other code for other arrangements.
is it possible to explain you the full story?

before the code you sent me, I was using the code below vba code, it was sent me one of the experienced friends here.

so I want to run both of them running but as I mentioned previously I am very very new about VBA codes and trying to learn (if I can as 50 years old man ). I added your codes to the page but I think the order of the codes I could not manage.

I am sure you are very busy but if you can help me, I will be very happy.

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



that was making that:
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"

 

[bahadir70]

I just checked separately only the code you sent me, but also not working