adapt code to various criteria column

Hi.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)If Target.Column = 2 Then
Call I_wish_it_was_only_in_Worksheet_Change_
Else
Exit Sub
End If
End Sub

Hi.
could someone help me adapt the code so that column "B", and "Crash," consider "Near-miss" and "Non-Compliance", like the "OR" of Excel, and the result happens in columns "T", "U", "V" and "W"?


tried the code below but is not working.

Sub I_wish_it_was_only_in_Worksheet_Change_()


Dim i As Long, n As Long
n = Range("B65536").End(xlUp).Row
For i = 2 To n
If Range("B" & i).Value = "" Then
Range("T:T, U:U, V:V, W:W" & i).Value = ""
ElseIf Range("B" & i).Value = "Crash" Or "Near-miss" Or "Non-Compliance" Then
Range("T:T, U:U, V:V, W:W" & i).Value = ""
Else
Range("T:T, U:U, V:V, W:W" & i).Value = "NA"
End If
Next i
End Sub

Cross-Post
http://www.mrexcel.com/forum/excel-q...ra-result.html

Hi marreco

Try this

Option Explicit

Sub I_wish_it_was_only_in_Worksheet_Change_()
    Dim i As Long, n As Long
    Dim myCells As Range

    n = Range("B65536").End(xlUp).Row
    For i = 2 To n
        If Range("B" & i).Value = "" Or Range("B" & i).Value = "Crash" _
                Or Range("B" & i).Value = "Near-miss" Or Range("B" & i).Value = "Non-Compliange" Then
            Set myCells = Union(Range("T" & i), Range("U" & i), Range("V" & i), Range("W" & i))
            Application.EnableEvents = False
            myCells.Value = ""
            Application.EnableEvents = True
        Else
            Set myCells = Union(Range("T" & i), Range("U" & i), Range("V" & i), Range("W" & i))
            Application.EnableEvents = False
            myCells.Value = "N/A"
            Application.EnableEvents = True
        End If
    Next i
End Sub

or this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim n As Long
    Dim i As Long
    Dim myCells As Range
    n = Range("B65536").End(xlUp).Row
    If Not Intersect(Target, Range("B2:B" & n)) Is Nothing Then
        i = Target.Row
        If Target.Value = "" Or Target.Value = "Crash" _
                Or Target.Value = "Near-miss" Or Target.Value = "Non-Compliange" Then
            Set myCells = Union(Range("T" & i), Range("U" & i), Range("V" & i), Range("W" & i))
            Application.EnableEvents = False
            myCells.Value = ""
            Application.EnableEvents = True
        Else
            Set myCells = Union(Range("T" & i), Range("U" & i), Range("V" & i), Range("W" & i))
            Application.EnableEvents = False
            myCells.Value = "N/A"
            Application.EnableEvents = True
        End If

        Exit Sub
    End If
End Sub

Hi.

I'm trying to run the code but nothing happens!

Thank you!!

Hi marreco

The code is in the attached and appears to work for me.

Hi.
It was great!

Very many thanks!

You're welcome...glad I could help. Thanks for the Rep.